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!

 

[jasonb75]

You will not be able to do that with sumifs, it would need to be a sumproduct array. Not recommended with entire columns, I've set this up for 200 rows. Can be adjusted as needed.
Note that I've assumed the blanks are formula blanks, if they are empty cells then this is likely to double the results as an empty cell is both blank and 0 with this type of formula.

=SUMPRODUCT('July - Present'!O2:O200,
(EOMONTH('July - Present'!A2:A200,0)=DATE(2021,10,31))*
(('July - Present'!Q2:Q200="")+('July - Present'!Q2:Q200=0))*
(('July - Present'!R2:R200="")+('July - Present'!R2:R200=0))*
(('July - Present'!S2:S200="")+('July - Present'!S2:S200=0)))

 

[JEH105]

You will not be able to do that with sumifs, it would need to be a sumproduct array. Not recommended with entire columns, I've set this up for 200 rows. Can be adjusted as needed.
Note that I've assumed the blanks are formula blanks, if they are empty cells then this is likely to double the results as an empty cell is both blank and 0 with this type of formula.

=SUMPRODUCT('July - Present'!O2:O200,
(EOMONTH('July - Present'!A2:A200,0)=DATE(2021,10,31))*
(('July - Present'!Q2:Q200="")+('July - Present'!Q2:Q200=0))*
(('July - Present'!R2:R200="")+('July - Present'!R2:R200=0))*
(('July - Present'!S2:S200="")+('July - Present'!S2:S200=0)))

Hi Jason,

I tried the formula, but unfortunately returned with an error.

Column Q, R, and S contain numeric values from 0-1000 and empty cells. I should've clarified that by blank I meant empty cells. So, what I need is to get the sum of column O, only if columns Q, R, and S are empty or 0 and as long as it's within the date range. Maybe there's another way? I'll keep playing around with SUMPRODUCT, maybe something works. :/

Thank you!

 

[RoryA]

I think you want:

=SUMPRODUCT('July - Present'!O2:O200*('July - Present'!A2:A200>=DATE(2021,10,1))*(
'July - Present'!A2:A200<=DATE(2021,10,31))*
('July - Present'!Q2:Q200=0)*('July - Present'!R2:R200=0)*('July - Present'!S2:S200=0))

 

[JEH105]

I tried using the SUMPRODUCT formula below as well, it didn't return with an error, but it's just not counting "0" as a criteria.

=SUM(SUMPRODUCT(('July - Present'!A2:A90000>=DATE(2021,10,1))*
('July - Present'!A2:A90000<=DATE(2021,10,31))*
('July - Present'!Q2:Q90000=0)*
('July - Present'!Q2:Q90000="")*
('July - Present'!R2:R90000=0)*
('July - Present'!R2:R90000="")*
('July - Present'!S2:S90000=0)*
('July - Present'!S2:S90000="")*
'July - Present'!O2:O90000))

It identifies the empty cells as criteria and the dates, but not the zeros. Any ideas?

Thank you!

 

[JEH105]

I think you want:

=SUMPRODUCT('July - Present'!O2:O200*('July - Present'!A2:A200>=DATE(2021,10,1))*(
'July - Present'!A2:A200<=DATE(2021,10,31))*
('July - Present'!Q2:Q200=0)*('July - Present'!R2:R200=0)*('July - Present'!S2:S200=0))

OMG YES! Thank you so much. So, just for future reference, I don't have to add "" criteria to count the empty cells in SUMPRODUCT?

 

[jasonb75]

I tried the formula, but unfortunately returned with an error.

Sorry, that was my bad for not testing it first. I forgot that eomonth needs a bit of persuasion to work with an array of dates.

The method that I was using, corrected and with the check for blanks removed would be

=SUMPRODUCT('July - Present'!O2:O200,
(EOMONTH(N(IF({1},'July - Present'!A2:A200)),0)=DATE(2021,10,31))*
('July - Present'!Q2:Q200=0)*
('July - Present'!R2:R200=0)*
('July - Present'!S2:S200=0))

If you are using excel 2019 or older then you would need to array confirm it with Ctrl Shift Enter.

 

[JEH105]

I forgot that eomonth ne

Sorry, that was my bad for not testing it first. I forgot that eomonth needs a bit of persuasion to work with an array of dates.

The method that I was using, corrected and with the check for blanks removed would be

=SUMPRODUCT('July - Present'!O2:O200,
(EOMONTH(N(IF({1},'July - Present'!A2:A200)),0)=DATE(2021,10,31))*
('July - Present'!Q2:Q200=0)*
('July - Present'!R2:R200=0)*
('July - Present'!S2:S200=0))

If you are using excel 2019 or older then you would need to array confirm it with Ctrl Shift Enter.

Gotcha, thank you again!

 

[JEH105]

@jasonb75 or @RoryA

I'm not sure if I should create a new post for this one, but I have another similar one that I can't figure out. Smh.

=SUMIFS('July - Present'!R:R,
'July - Present'!A:A,">="&DATE(2021,10,1),
'July - Present'!A:A,"<="&DATE(2021,10,31),
'July - Present'!F:F,"*EMEA*",
'July - Present'!N:N,"<"&'July - Present'!R:R)

Everything seems to work on this one except for that last criteria listed (red font). The last criteria listed is to only sum if column P is less than column Q, along with the other 2 criterias listed before. The formula returns as 0.

I tested it alone without the last criteria and it works. It also works if I use a specific cell in the Q column, instead of the whole Q:Q column. I've also double checked the data to ensure there is data that falls under this criteria and applied some dummy data to test it out.

Thank you for your time!

 

[jasonb75]

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.