Hi, I am going to try to explain this as best as I can to get as much assistance from this forum as possible. I am modifying a report for my Manager because the data we were logging/tracking in 1&2 quarter has changed. We were using COUNTIFS because we only needed to count the data, now we need a sum of a particular range and specific criteria.

OLD tracking:

=COUNTIFS('Raw Data'!P:P,"<>In-progress",'Raw Data'!M:M,"Triggers",'Raw Data'!G:G,"BB Mass")P:P is the final disposition (Done, Follow-up etc)

M:M is the type of review (Trigger, New product etc)

G:G is the business segment who sent us the request (BB Mass, Large Corp etc)New tracking - Please note many of the columns (from above) may have changed. Going forward we're tracking the # of errors we correct and we're assigning a level of severity of each (based on the amount of time it takes to correct them). Column P:P (from above) has been removed.

I am trying to get a sum of Column N:N (actual # of errors inputted here), for only "Triggers" (column M:M) business segment BB Mass (column G:G) THE ISSUE IS because there are three levels of severity we've broken them out in three different columns - column N:N is dedicated solely for Regulatory (error level). There is no text (ie regulatory) in column N:N ONLY the # of regulatory errors we had to fix.

I am in need of a formula that will give me a sum of all Regulatory errors for Triggers and BB Mass ONLY.

I gave it a try but this is not working:

=SUMIFS('Raw Data'!N:N,'Raw Data'!G:G,"BB Mass",'Raw Data'!M:M,"Triggers",'Raw Data'!N:N,">=1")

I am pretty sure my issue is the <=1 but I'll leave that up to you EXPERTS to correct. Thank you!

I tried to share as much info as possible!

I gave it a try but this is not working:In what way is not working?

Does it give an error message?

Does it not return anything?

Looking at your SUMIFS() formula, it appears to be correct

but with no data to work with, not completely sure.Try breaking it down piece by piece and see where it goes wrong.

=SUMIFS('Raw Data'!N:N,'Raw Data'!G:G,"BB Mass")

Then try

=SUMIFS('Raw Data'!N:N,'Raw Data'!G:G,"BB Mass",'Raw Data'!M:M,"Triggers")

I am pretty sure my issue is the <=1There is no less than or equal to 1 in your formula,

there is a greater than or equal to 1.Also, make sure all your TEXT strings, BB Mass and Triggers do not have a

hidden space character at the end or beginning.

Excel considers a space a valid character so

Triggers is not the same as Triggers<Space>

and BB<Space>Mass is not the same as BB<Space><Space>Mass

MIKE

Just another thought on your formula. You probably do not need the last clause: 'Raw Data'!N:N,">=1"

SUMIFS() will sum all the values, if there are any,

and will ignore Blanks and TEXT, so it's not needed.MIKE

message edited by mmcconaghy

It might be needed. What if there are values in Column N that are <1 which he wants to exclude from the SUM?

(I'm not near Excel at the moment, so I can't test anything)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you mmcconaghy & DerbyDad03 - mmcconaghy your first recommendation to remove the <=1 and/or <1 (I tried the formula both ways). Once removed I got the total that I was looking for! You all ROCK! Thank you again!

message edited by HarrisLyfe2016

DerbyDad There is/was a bit of confusion about the <=1

Although HarrisLyfe2016 speaks of <1 or <=1

the formula does not use <=1

the formula uses >=1

(See original post)

and since the purpose of the formula was to simply

sum the total number or errors, which had occurred.In this situation, I felt the likelihood of a negative number was nill.

MIKE

message edited by mmcconaghy

No argument, but... re:

In this situation, I felt the likelihood of a negative number was nill..5 is not negative, but is less than 1.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History