'Excel COUNTIFS checking for blank cells
I have a COUNTIFS functions that checks a few things. These include;
- Date
- Client
- Whether a certain range is blank
The current function I have is this
=COUNTIFS(DateRange, ">=" & StartPeriod, DateRange, "<" & EndPeriod, ClientRange, "=" $ Client, DateSent, "<> '' ")
- DateRange is the range of all date,
- StartPeriod and EndPeriod are the date constraints,
- ClientRange is the range of all client names,
- Client is who will be filter for,
- DateSent is when the item was sent out. This is either a blank value or a date
I am trying to make sure that the DateSent is not blank, but I can't figure out how to check for it since I can't use <> "" due to the requirements of the quotes.
How can I get around this requirement in a COUNTIFS?
NOTE: Adding a helper column is not a solution I can use, the table I have is what I have to work with.
Solution 1:[1]
Try using the inverse logic. I.E. not blank = cell has content or in excel <>"" = "*"
=COUNTIFS( ......... ,DataSent,"*")
Solution 2:[2]
Use SUMPRODUCT
=SUMPRODUCT(--(A3:A11>=G1),--(A3:A11<=G2),--(B3:B11=G3),--(C3:C11<>""))
If you can't use "" for some reason then use ISNUMBER (Assuming dates are numbers, or ISTEXT if text in the sent column) e.g.
=SUMPRODUCT(--(A3:A11>=G1),--(A3:A11<=G2),--(B3:B11=G3),--(ISNUMBER(C3:C11)))
Solution 3:[3]
"<>" is fine to achieve what you need.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | urdearboy |
| Solution 2 | |
| Solution 3 | KabotakRunner |