Countif Function not counting all the values in range

Resolved · Medium Priority · Version 2016

Mon 4 Oct 2021, 20:37 Delegate Kay said...

Kay has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course

Countif Function not counting all the values in range

Hi,

I am currently setting up a COUNTIF formula as below;

=COUNTIF($A$2:$A$1566,"*"&C4&"*")

the raw data range is from A2 TO A1566
C4 is a number, anything from 2577 to 819979

the filter that I am using in the raw data is search function rather then "equal" or "contain"

I see the following issues;

a) The countif for 2577 is returning 2 rather than 0 as it is counting everything that contains 2577 in the line, i.e. 2055;22577;1119088, 2257706 in my raw data

b) The countif for 1021 tells me there is 90 in the raw data however the countif is only returning 73

Is there anything I need to change on my raw data filters or any suggestions how best to fix this please?

Your help will be greatly appreciated

Wed 6 Oct 2021, 09:33 Trainer Marius said...

RE: Countif Function not counting all the values in range

Hello Kay,

Thank you for your question. If I understand correctly, you want to count exact values in column A (e.g. 2577) instead of values containing 2577 (e.g. 5425773). Your current formula will count any values containing / including 2577.

To count exact values, try

=COUNTIF($A$2:$A$1566,C4)

where C4 is the exact value to count. The * symbols in your original formula will include other numbers alongside the C4 value.

I hope this helps.

Kind regards
Marius Barnard
STL