COUNTIF returns wrong result

1
votes

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.

1
excelexcel-formulacountifworksheet-functionsumproduct
Use COUNTIF with wildcards. – BigBen
I'm not sure if wild cards help with abbreviations because they don't all use the first letters of the name of the state (e.g. MD=Maryland, but MA* would get Maine, Massachusetts and Maryland) – Tom Sharpe

1 Answers

1
votes

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.