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.
<tbody>
</tbody>
I really appreciate any help anyone can give and thank you in advance for your time!!
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!!
