Hello,
I have two formulas; one is doing what I need it to do for the first 6 rows but for the rest of the rows it is returning a 0 and I cant figure out why. The second formula provides a total count for all of the rows but is less dynamic and I need help adding in some extra criteria to narrow down the results.

On the "Assigned Audits" tabs in Range "D2-D2000" I have a list of codes. The first two digits of the code represents a State. In Range J2-J2000 is an indicator if the record has been reviewed.
On the "StateCodes" tab in Range A2-A51 I have a list of codes to reference.

The formula below is attempting to obtain a count for each state code if the record has been reviewed. This formula is giving the correct totals for the first 6 records and then it gives 0 even though there are records for most of the other states. I checked the formatting (Leading and trailing spaces, Text, Number, etc.)

First Formula: =SUMPRODUCT((('Assigned Audits'!$J$2:$J$2000<>"")*(LEFT('Assigned Audits'!$D$2:$D$2000,2)=StateCodes!$A2)))

The second formula I attempted is providing the total count of all records based on State Code. It does not have added criteria based on if the record has been reviewed or not (yet- I need this piece to be added but not sure how to accomplish it if this is the formula I have to go with)

=SUM(IF(ISNUMBER(MATCH(LEFT(Claim,2)+0,{1},0)),1,""))
Control+Shift+Enter

Below is what the worksheet looks like and how different the results are.


Code State Count Audited by State Count Assigned by State Q1 Q2 Q3 Q4
01 Alabama 12 13
02 Arizona 15 29
03 Arkansas 12 21
04 California 58 130
05 Colorado 18 35
06 Connecticut 3 3
07 Delaware 0 4
08 District of Columbia 0 0
09 Florida 0 0
10 Georgia 0 13
11 Idaho 0 8
12 Illinois 0 9

I really appreciate any help anyone can give and thank you in advance for your time!!