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
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
