COUNTIFS returns incorrect result

[GraH - Guido]

Hi,

First time for me to post a question. Never thought I would need to do this for a rather simple formula using COUNTIFS. But a colleague asked me to explain this, and I really cannot. And since yesterday it has kept me busy. Thus calling for help here.

Presenting the case:

• there is some customer data in the table tRefData
• we need some simple counters that check basic criteria in a few columns
• one of these counters for which we have used COUNTIFS returns however a wrong result:
  
  • =COUNTIFS(tRefData[Matched Ref],"<>",tRefData[New Queue],"",tRefData[Existing Queue],"<>") returns 912
  • =SUMPRODUCT((tRefData[Matched Ref]<>"")*(tRefData[New Queue]="")*(tRefData[Existing Queue]<>"")) returns 778

When filtering the data (the real life data is about 200K records) the sumproduct formula is confirmed. The countifs is not.

The data is the result of an imported access DB query. This query sets the blanks for all the columns in the same way via an if statement in access that uses "" as argument.
Strangely enough, when I delete the content of the blank cells in the column [Existing Queue], both formulas return the same and correct result.
But I do not get this. Why would the argument <>"" work in sumproduct and not in countifs for this 1 argument? While in countifs it does work for the first argument tRefData[Matched Ref],"<>"?

To be clear on the expectations: I do not need help solving it. We are using the sumproduct formula, but my colleague and I would really like to understand what is going on. Does some-one know or did you experienced the same? I tested the file in versions 2010 and 2016. And luckily the behaviour is consistent.

Enjoy the day.

 

[AliGW]

It's very simple (if somewhat unsatisfactory), I think: COUNTIFS does not take account of filtered data. SUMPRODUCT does.

 

[vletm]

GraH - Guido
From Syntax:
a) if criteria is an empty cell, COUNTIFS treats it as a zero (0) value
b) SUMPRODUCT treats array entries that are not numeric as if they were zeros.
empty or not?
I cleared cell D14.
After that, I compared D14 and D15 ... D14 = empty and D15 = "".

COUNTIFS - empty cells
SUMPRODUCT - not numeric

 

[pecoflyer]

As far as I can see the "empty cells in col F are not empty at all but contain the string :space""(which is text). COUNTIFS does not count these as empty while SUMPRODUCT does. If you clean the "empty cells" in col F, you get the same as sumproduct

 

[GraH - Guido]

All, thanks for the replies. I was being ignorant for syntax subtleties as it turns out. Meanwhile my colleague figured it also out.

It's very simple (if somewhat unsatisfactory), I think: COUNTIFS does not take account of filtered data. SUMPRODUCT does.

Dear, Ali, data is not filtered and the difference occurred without setting any filter.

GraH - Guido
From Syntax:
a) if criteria is an empty cell, COUNTIFS treats it as a zero (0) value
b) SUMPRODUCT treats array entries that are not numeric as if they were zeros.
empty or not?
I cleared cell D14.
After that, I compared D14 and D15 ... D14 = empty and D15 = "".
COUNTIFS - empty cells
SUMPRODUCT - not numeric

@vletm, I think you explained what I was after. I also cleared the data in column F and it worked as I explained in the post. Yet the filter showed those records as blank in both columns where we tested <>"". This confused more then anything. I would have expected to see something revealing like " " or so to explain the difference.

As far as I can see the "empty cells in col F are not empty at all but contain the string :space""(which is text). COUNTIFS does not count these as empty while SUMPRODUCT does. If you clean the "empty cells" in col F, you get the same as sumproduct

Hi pecoflyer, like in my post I suspected there was something in the column, because indeed after clearing I got the same result. Would have thought the applying the filter would reveal that. But you've also explained the why. What I was after.

Furthermore you (vletm and pecoflyer) confirm the root cause is to be found in the export from the DB. The columns are NOT filled in the same way. While we did look at that as well, and did not notice a difference. Will have to put our glasses on.
Just learned to revisit the syntax help when strange things occur. there is an explanation available. Sounds obvious enough, yet I hardly ever do that.

 

[shrivallabha]

It's very simple (if somewhat unsatisfactory), I think: COUNTIFS does not take account of filtered data. SUMPRODUCT does.

Not correct. Both formulas do not have capacity to handle filters. AGGREGATE and SUBTOTAL can.

 

[AliGW]

My mistake.

 

[pecoflyer]

FYI I just ran =code(F2) and then error values (#VALUE) came popping up for most of the "empty" cells where others returned the code of the first character as usual

 

[GraH - Guido]

pecoflyer, that is funny, I just did that to see what it would do