Formula not counting correctly

Morena

Hi Guys

Please note the formula. see pic1

I am trying to count BS Numbers excluding zeros and blanks

Pic1

However it's not returning the correct count, for some reason the formula is not counting some of the BS Numbers, see pic 2, the ones i circled in blue (see pic2), for formula is not counting them, i want the formula to count all BS number excluding zero and blanks. Please advise

Pic2

Paul Newcome

That is because you have mixed data types (some rows are numbers and some rows are text). This will always throw things off. You are going to need to insert a text/number column (can be hidden after setting up) and use a formula to convert every row into text (add quote quote).

=[BS Number]@row + ""

Then you can reference this helper column in your COUNTIFS.

Morena

@Paul Newcome

Hi Paul. Thanx for the advice

Paul Newcome

Happy to help. 👍️