Need Zeros and Blanks as a Criteria in SUMIFS

[JEH105]

I'm stuck! I've been trying to figure this out for hours now. I have the following formula below:

=SUMIFS('July - Present'!O:O,
'July - Present'!A:A,">="&DATE(2021,10,1),
'July - Present'!A:A,"<="&DATE(2021,10,31),
'July - Present'!Q:Q,"",
'July - Present'!R:R,"",
'July - Present'!S:S,"")

The formula is to pull the sum of subjects on column O, only if: 1. It's in between the date range provided 2. Columns Q, R, and S are blank or have a 0.

The issue is that I don't know how to add zero as part of the criteria. I have tried nesting ISNUMBER, COUNTA, COUNT, even adding with "+" another SUMIF formula just for 0s, but nothing seems to work.

Will someone please help me?! I need both ZERO and BLANKS to be counted as a criteria in this formula. Thank you in advance!

 

[JEH105]

Again, it would require sumproduct, not sumifs. With sumifs, the criteria can only be a single value. You can use multiple values with SUM(SUMIFS(...)) when it is an = criteria, but with < or > you will encounter numerous errors.

Give it a go with sumproduct and see how you get on, feel free to scream if you get stuck.

This is really good to know. It worked! Thanks again!

Formula that worked:

=SUMPRODUCT('July - Present'!Q2:Q200000*
('July - Present'!A2:A200000>=DATE(2021,10,1))*
('July - Present'!A2:A200000<=DATE(2021,10,31))*
('July - Present'!F2:F200000="EMEA")*
('July - Present'!N2:N200000>0)*
('July - Present'!N2:N200000<'July - Present'!Q2:Q200000))

 

[RoryA]

A SUM(SUMIFS(..)) construction should work there too, FWIW.

Do you really have 200000 rows of data? If not, that's a lot of unnecessary processing being done. I'd also suggest you update your forum profile to let people know what version of Office you are using. If you have 365 there are possibly better options like FILTER that you could use.

 

[jasonb75]

A SUM(SUMIFS(..)) construction should work there too, FWIW.

It would work but is more than likely to give incorrect results.

 

[RoryA]

Why? (ignoring the fact that if it gives incorrect results, it doesn't work )

 

[jasonb75]

Look at the last array in the formula, it is comparing row 2 to row 2, row 3 to row 3, etc. SUM(SUMIF( would compare row 2 to all rows, row 3 to all rows, etc.

(ignoring the fact that if it gives incorrect results, it doesn't work )

It does with my logic, Rory My theory is that if it gives a result it works, just not correctly / as expected (although I do tend to reserve this more for when people ask the wrong question and expect you to read their minds to provided the right answer).

 

[RoryA]

SUM(SUMIF( would compare row 2 to all rows, row 3 to all rows

To be honest, that's what I thought was wanted. Must be force of habit...

Having re-read the formula, I admit it doesn't seem likely!

 

[JEH105]

A SUM(SUMIFS(..)) construction should work there too, FWIW.

Do you really have 200000 rows of data? If not, that's a lot of unnecessary processing being done. I'd also suggest you update your forum profile to let people know what version of Office you are using. If you have 365 there are possibly better options like FILTER that you could use.

Got it. I'll be updating my forum profile ASAP. I do have 365 and yes... 180,000 rows of data so far.

 

[JEH105]

To be honest, that's what I thought was wanted. Must be force of habit...

Having re-read the formula, I admit it doesn't seem likely!

Thanks guys, and yes to clarify it was row to row comparison vs column sum to column sum (if that makes any sense). Thank you both for your help!