counting both blank cells and cells with a specified value together, where both are based on other multiple criteria

[Helen Wills]

Hi. I have a formula that works so far....

=COUNTIFS('sheet name'!U5:U78, "2020", 'sheet name'!V5:V78, "q3", 'sheet name'!Y5:Y78, "delayed")

However, in the next one i want to count all that are pending or the cell is blank ie

=COUNTIFS('sheet name'!U5:U78, "2020", 'sheet name'!V5:V78, "q3", 'sheet name'!Y5:Y78, "pending") ADDED TO
COUNTIFS('sheet name'!U5:U78, "2020", 'sheet name'!V5:V78, "q3", 'sheet name'!Y5:Y78, "blank cells")

I know that "blank cells" is not what i put here, but i am struggling how to use countblank as well as all the other criteria. Can anyone help?

 

[Eric W]

Try:

=SUM(COUNTIFS('sheet name'!U5:U78, "2020", 'sheet name'!V5:V78, "q3", 'sheet name'!Y5:Y78,{"pending",""}))

 

[Helen Wills]

Try:

=SUM(COUNTIFS('sheet name'!U5:U78, "2020", 'sheet name'!V5:V78, "q3", 'sheet name'!Y5:Y78,{"pending",""}))

Worked a charm - thanks so much! x

 

[Eric W]

Glad it works for you! Thanks for the update.

 

[Helen Wills]

Glad it works for you! Thanks for the update.

apologies. I've just noticed it hasn't worked... it's counting all projects in pending. I'd expect P10 (total pending) to say 4 if G10 (completed) is showing 6 and D10 (total) is 10. sorry to ask again!

 

[Eric W]

Without seeing your source data, it's hard to know what the issue is. Is the data in column Y text, or the result of formulas? Is a blank cell truly blank ("") or does it have a space (" ")? Can you show your data with the XL2BB tool? (See the link in the reply box.)

If your expected value is D10 less G10, can you use this formula?

=D10-G10