Can I make this smaller?

[JBJones82]

I have Several Countifs and want to make it into one saying if this area was assigned by one of these people count the job, I have tried many different ways and can not seem to make it work. unless I have it search for each assigning Person one by one. Is there a way to make this Formula much smaller so if I have to edit or add stuff I do not have to go back and do the entire Formula over?
=COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$38)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$39)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$40)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$41)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$42)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!I$43)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$44)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$45)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$46)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$47)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$48)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$49)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$50)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$51)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$52)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$53)+COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$54)

 

[oldbrewer]

much easier to show us some spoof data and say what your desired output is...........

 

[Domenic]

Try...

=SUMPRODUCT(COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$38:$I$54))

Hope this helps!

 

[JBJones82]

Thanks so much!
Why does this work
=SUMPRODUCT(COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$38:$I$54))
and not this
=COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$38:$I$54)

 

[Domenic]

=COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$38:$I$54)

The above formula returns an array of values, not a single value. As such, it needs to be passed to another function such as SUMPRODUCT to be summed. You can see for yourself by entering the formula in an array of cells...

1) Select a 17 x 1 vertical range of cells, let's say A1:A17.

2) Enter the COUNTIFS formula (without SUMPRODUCT), and confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER.

As you can see, you now have a list of values returned by COUNTIFS. It's this array of values that is passed to SUMPRODUCT, and which is then summed.

 

[JBJones82]

I take it back it is not working it is still counting other info

Trying to take this (see below) and count what is Closed, cancelled and pending for certain jobs the only thing that is still constant in the report is who assigned the call out by the first 3 letters. But it keeps counting every call on the list, Right now I am using this Formula: =SUMPRODUCT(COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$38:$I$54)) I:38:I54 are the CSG IDs I am looking for. and I need to add them to the table based on Local Info. The Reason will change and there are 50+ or I would just count by the M1.

[TABLE="width: 541"]
<tbody>[TR]
[TD]Job Status[/TD]
[TD]SMS Tech ID[/TD]
[TD]Reason[/TD]
[TD]Order Date/Time[/TD]
[TD]LOCINFO1[/TD]
[TD]ORDERINGOPERATORID[/TD]
[TD]SMS Job Number[/TD]
[TD]Over 24hrs[/TD]
[/TR]
[TR]
[TD]Rescheduled[/TD]
[TD][/TD]
[TD]A1-CLI LEAKAGE<100[/TD]
[TD="align: center"]########[/TD]
[TD]V26[/TD]
[TD]BRM 0FGP[/TD]
[TD="align: right"]531668[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rescheduled[/TD]
[TD][/TD]
[TD]A1-CLI LEAKAGE<100[/TD]
[TD="align: center"]########[/TD]
[TD]M32B[/TD]
[TD]YZC 20L2[/TD]
[TD="align: right"]551693[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD="align: right"]6865[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]K11B[/TD]
[TD]3UT 4265[/TD]
[TD="align: right"]598910[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD="align: right"]5367[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]H015W[/TD]
[TD]A46 4276[/TD]
[TD="align: right"]604837[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD="align: right"]5328[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]S29A[/TD]
[TD]A46 4276[/TD]
[TD="align: right"]605975[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assign Confirmed[/TD]
[TD="align: right"]5327[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]V22[/TD]
[TD]PLZ 42RN[/TD]
[TD="align: right"]607986[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assign Confirmed[/TD]
[TD="align: right"]5344[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]R19[/TD]
[TD]MOO 42P0[/TD]
[TD="align: right"]608943[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD="align: right"]5311[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]D108[/TD]
[TD]PLZ 42RN[/TD]
[TD="align: right"]609049[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assign Confirmed[/TD]
[TD="align: right"]5370[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]C073W[/TD]
[TD]HOD 43NX[/TD]
[TD="align: right"]609122[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD="align: right"]5328[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]S106[/TD]
[TD]HOD 43NX[/TD]
[TD="align: right"]611347[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assign Confirmed[/TD]
[TD="align: right"]5327[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]B18A[/TD]
[TD]PLZ 42RN[/TD]
[TD="align: right"]612273[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assign Confirmed[/TD]
[TD="align: right"]5367[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]K006W[/TD]
[TD]PLZ 42RN[/TD]
[TD="align: right"]612313[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assign Confirmed[/TD]
[TD="align: right"]5370[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]C058W[/TD]
[TD]HOD 43NX[/TD]
[TD="align: right"]612767[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assign Confirmed[/TD]
[TD="align: right"]5367[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]H061W[/TD]
[TD]PWS 42T3[/TD]
[TD="align: right"]618412[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assign Confirmed[/TD]
[TD="align: right"]5325[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]AD107[/TD]
[TD]JHK 428X[/TD]
[TD="align: right"]618441[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD="align: right"]5407[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]A051W[/TD]
[TD]PWS 42T3[/TD]
[TD="align: right"]618450[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD="align: right"]5328[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]M07[/TD]
[TD]JHK 428X[/TD]
[TD="align: right"]618498[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assign Confirmed[/TD]
[TD="align: right"]5367[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]K016W[/TD]
[TD]ZUW 43B3[/TD]
[TD="align: right"]618505[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assign Confirmed[/TD]
[TD="align: right"]5396[/TD]
[TD]??? CTX-ED[/TD]
[TD="align: center"]########[/TD]
[TD]AB15[/TD]
[TD]JHK 428X[/TD]
[TD="align: right"]618812[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assign Confirmed[/TD]
[TD="align: right"]5407[/TD]
[TD]M1 - MAINT WORK CUS IMPACTING[/TD]
[TD="align: center"]########[/TD]
[TD]N045W[/TD]
[TD]ZUW 43B3[/TD]
[TD="align: right"]618921[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I am Searching for
[TABLE="width: 541"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 165"]
<tbody>[TR]
[TD="colspan: 2"]TOC MEMBER[/TD]
[/TR]
[TR]
[TD]CSG ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]WRC*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]CYT*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]J9B*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]JHK*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]MG9*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]MOO*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]PWS*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]D5*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]R9S*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]3UT*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]ZUW*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]420*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]PLZ*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]A46*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]HOD*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]XIO*[/TD]
[TD]No Name[/TD]
[/TR]
[TR]
[TD]HTJ*[/TD]
[TD]No Name[/TD]
[/TR]
</tbody>[/TABLE]

where the results go and then it is totaled
[TABLE="width: 399"]
<tbody>[TR]
[TD]Node[/TD]
[TD]Area[/TD]
[TD]Open[/TD]
[TD]Closed[/TD]
[TD]Over[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]A*[/TD]
[TD]North West[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]C*[/TD]
[TD]North West[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]D*[/TD]
[TD]North West[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]T*[/TD]
[TD]North West[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AF*[/TD]
[TD]North West[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]B101[/TD]
[TD]North West[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]B109[/TD]
[TD]North West[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]B131[/TD]
[TD]North West[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]B133[/TD]
[TD]North West[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]H*W[/TD]
[TD]Killeen[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]K*W[/TD]
[TD]Killeen[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]C*W[/TD]
[TD]Killeen[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]F*W[/TD]
[TD]Killeen[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]B*W[/TD]
[TD]Killeen[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A*W[/TD]
[TD]Waco[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]E*W[/TD]
[TD]Waco[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]N*W[/TD]
[TD]Waco[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]S*W[/TD]
[TD]Waco[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]W*W[/TD]
[TD]Waco[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]T*W[/TD]
[TD]Waco[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]K*[/TD]
[TD]South[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]L*[/TD]
[TD]South[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]V*[/TD]
[TD]South[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]S*[/TD]
[TD]South[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]H*[/TD]
[TD]South[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]M*[/TD]
[TD]South[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]B*[/TD]
[TD]South[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Q*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]J*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]N*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]P*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]U*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Y*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AC*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AD*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AK*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AJ*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AB*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AH*[/TD]
[TD]South Ctx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]F*[/TD]
[TD]North East[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]G*[/TD]
[TD]North East[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]R*[/TD]
[TD]North East[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]W*[/TD]
[TD]North East[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]X*[/TD]
[TD]North East[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[JBJones82]

Try...

=SUMPRODUCT(COUNTIFS('Filtered Report'!$I:$I,Admin!$A56,'Filtered Report'!J:J,Admin!$I$38:$I$54))

Hope this helps!

Ok So that Formula is working but for some reason it is counting adding a 2 extra to the count in the totals by the node and I can not see where this is coming from.

 

[JBJones82]

Ok I figured it out it was an issue with the Wildcard I just need to make sure it is not looking at other nodes and search....Thanks again for that Formula, it was a big help

 

[Domenic]

That's great, I'm glad you've sorted it out.

Cheers!