CODEWITHSUNDEEP
excelexcel-formulacountifworksheet-functionsumproduct
jimmy2891
jimmy2891

Reputation: 11

COUNTIF returns wrong result

enter image description here I have 3,900 rows with multiple states listed in each cell. I want to use COUNTIF or SUMPRODUCT to count the number of cells that mention each of the 50 states. The formula I used is:

=SUMPRODUCT(($C$2:$C$3896=P3)+0)

When I total the number of cells that mention each state, it equals approximately 2,500 (1400 less than it should). I have already done the basics like trim the data and account for cells with <255 characters. Any help is greatly appreciated.

Upvotes: 1

Views: 685

Answers (1)

ZygD
ZygD

Reputation: 24386

The following array formula works for me (array formulas are entered not using regular Enter, but instead Ctrl + Shift + Enter )

=SUM(IFERROR(IF(FIND(O2,$C$2:$C$3896),1),0))+SUM(IFERROR(IF(FIND(P2,$C$2:$C$3896),1),0))

enter image description here

Note: you will not find Illinois if only the first letter is capitalized (Il) in its abbreviation. The abbreviation should be IL in order to find it. The function FIND is case sensitive.

Upvotes: 1

Related Questions