Excel COUNTIFS functions started returning a "0"

Question

I have had the following countifs functions working fine for a few months, however they have randomly stopped working and started returning a 0 figure.

Ive tried a few different troubleshooting processes such as making sure the calculation is set to automatic, but with no luck.

=COUNTIFS(C1:C37, "23****",M1:M37, "y")

=COUNTIFS(C1:C37, "23****", M1:M37, "=")

Can confirm that there is data in the cells starting with "23" so that is not the issue.

Any and all advice is welcome!

Answer 1

It is trying to count how many cells have data that start with 21 and have four random numbers against it. IE, if cell C2, C3 and C4 have the values 230567, 218772 and 231921 in them respectfully then the formula should return a 2, however, it is returning a 0.

Even if I use ? as opposed to *, I still return a 0 result.

Answer 2

Wow! Thank you, this worked.

Its very weird, as the formulas were working for about two months prior to returning a 0. Absolutely no idea what happened there.

Answer 3

Hi,

Let's figure out what's wrong, OK?

What is the "23****" in =COUNTIFS(C1:C5, "23****",M1:M5, "y") trying to find?

A few points:

This formula is looking for all TEXT strings starting with 23. Although this works it is not the correct way to use the *.

If that were what you wanted, you should change the formula to look for "23*". In that case the formula would be =COUNTIFS(C1:C5, "23*",M1:M5, "y"). However, that change will not affect your result - if "23****" fails, so will "23*"!

Also, if you were looking for 23 followed by 4 characters the way to do that would be "23????". But since you are getting 0, this isn't even an issue.

Answer 4

Hi,

This works

=SUMPRODUCT((LEFT(A2:A6,2)=23&"")*(B2:B6="y"))

Hope this helps.

Answer 5

Tested your formula, it also doesn't work at my side.

You may try workaround.

=SUM(ISNUMBER(SEARCH("23",C1:C37))*(M1:M37="y"))

Or

=SUM((LEFT(C1:C37,2)="23")*(M1:M37="y"))