SUMPRODUCT working in some cells but not all

jbeet

New Member
Joined
Feb 1, 2015
Messages
7
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

<tbody>
</tbody>

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

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Watch MrExcel Video

Forum statistics

Threads
1,099,566
Messages
5,469,446
Members
406,652
Latest member
LJA

This Week's Hot Topics

Top