Solved Is SUMIFS the correct Excel formula?

June 15, 2016 at 12:04:18
Specs: Windows 64
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!


See More: Is SUMIFS the correct Excel formula?

Report •


#1
June 15, 2016 at 14:05:20
✔ Best Answer
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 <=1

There 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

http://www.skeptic.com/


Report •

#2
June 15, 2016 at 14:30:25
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#3
June 15, 2016 at 21:39:40
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.


Report •

Related Solutions

#4
June 16, 2016 at 06:53:55
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


Report •

#5
June 16, 2016 at 07:16:23
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
June 16, 2016 at 21:16:36
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.


Report •


Ask Question