How to Exclude Empty String in Countifs

Question

I have an excel sheet with 2000+ rows and 15 columns. I need to count the entries in a certain column based on conditions of other columns. Here's a sample of the sheet:

  A    |     B    |    C    |   D   |     E
-------------------------------------------------
Fruit  |  Origin  |  Price  |  Qty  |  Feedback
-------------------------------------------------
Apple  |  Amazon  |   100   |   4   |    Great
Banana |  India   |   100   |   1   |
Orange |  Africa  |   200   |   2   |    Good
Apple  |  Amazon  |   300   |   5   |
Guava  |  India   |   100   |   1   |    Great
Banana |  India   |   400   |   5   |
Orange |  India   |   200   |   6   |
Apple  |  Amazon  |   100   |   8   |    Good
Kiwi   |  Africa  |   300   |   5   |
Banana |  Africa  |   500   |   4   |
Apple  |  Amazon  |   100   |   1   |    Good
Orange |  Amazon  |   300   |   5   |
Kiwi   |  India   |   200   |   4   |
Guava  |  India   |   100   |   1   |    Good
Apple  |  Amazon  |   300   |   5   |    Good
Banana |  Africa  |   100   |   4   |
Apple  |  Amazon  |   200   |   1   |    Great
Guava  |  India   |   200   |   5   |
Orange |  Amazon  |   300   |   1   |    Good
Apple  |  Amazon  |   200   |   5   |    Good

Now I need to get the number of feedbacks for Apple Fruit with Origin as Amazon, Price greater than 100 and quantity greater than 2. So my formula would go as:

=COUNTIFS(A:A, "Apple", B:B, "Amazon", C:C, ">100", D:D, ">2")

Now I want to add another condition to get the feedbacks which are filled (not empty), so my formula would go like:

=COUNTIFS(A:A, "Apple", B:B, "Amazon", C:C, ">100", D:D, ">2", E:E, "<>")

Ideally, the above formula would yield a result of 3. Now if the blank cells in Column E are empty strings "", then the above formula returns 4, as the formula treats "" the same as filled value. Can anyone suggest a formula in which I would exclude the empty strings "" in my COUNTIF condition, so that my result comes as 3 even with the empty strings in Column E?

Appreciate the help in advance...

Answer 1

Nice little question and indeed an anoying issue. COUNTIFS() does support wildcards. A "?" stands for any single character whereas the "*" stands for any 0+ characters. Therefor combining those two wildcards into "?*" we are telling the function to test if the cell holds at least a single character (possibly more). Funny enough a zero-width string ="" is not triggered by this because, as by definition (and suggested by it's name) they are indeed zero-width strings.

I think with your sample data you'd want to return "2", right? Anyways, try:

=COUNTIFS(A:A,"Apple",B:B,"Amazon",C:C,">100",D:D,">2",E:E,"?*")

Answer 2

Consider using something like:

=SUMPRODUCT(--(A2:A100="Apple")*(B2:B100="Amazon")*(C2:C100>100)*(D2:D100>2)*(E2:E100<>""))

SUMPRODUCT() supports a very intuitive way to test for empties.

Where the only valid are row 16 and row 21.

EDIT#1:

Here are references:

Exceljet