SUMPRODUCT working in some cells but not all

[jbeet]

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.


[TABLE="width: 750"]
<tbody>[TR]
[TD]Code
[/TD]
[TD]State
[/TD]
[TD]Count Audited by State
[/TD]
[TD]Count Assigned by State
[/TD]
[TD]Q1
[/TD]
[TD]Q2
[/TD]
[TD]Q3
[/TD]
[TD]Q4
[/TD]
[/TR]
[TR]
[TD]01
[/TD]
[TD] Alabama
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02
[/TD]
[TD] Arizona
[/TD]
[TD]15
[/TD]
[TD]29
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03
[/TD]
[TD] Arkansas
[/TD]
[TD]12
[/TD]
[TD]21
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04
[/TD]
[TD] California
[/TD]
[TD]58
[/TD]
[TD]130
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05
[/TD]
[TD] Colorado
[/TD]
[TD]18
[/TD]
[TD]35
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06
[/TD]
[TD] Connecticut
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07
[/TD]
[TD] Delaware
[/TD]
[TD]0
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08
[/TD]
[TD] District of Columbia
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09
[/TD]
[TD] Florida
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD] Georgia
[/TD]
[TD]0
[/TD]
[TD]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD] Idaho
[/TD]
[TD]0
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD] Illinois
[/TD]
[TD]0
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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