Using COUNTIFS to match criteria in a range and also another cell

[angeloudaki]

Using cell references only, how would I count the number of times a value appears in a range and also matches another (fixed) criteria?

Example: I want to know how many times the value in H3 (table2) appears in table1range B2:D15 where their SS7 code is also the same as that in J1 (table2)

I used:

=COUNTIFS(C3:E16,H4,B3:B16,J2)

but this returns a

#VALUE!

error

Table1:
[TABLE="class: grid, width: 387"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Module[/TD]
[TD]Staff 1[/TD]
[TD]Staff 2[/TD]
[TD]Staff 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SS7100[/TD]
[TD]PERSON1[/TD]
[TD]PERSON2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SS7100[/TD]
[TD]PERSON3[/TD]
[TD]PERSON2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]SS7300[/TD]
[TD]PERSON3[/TD]
[TD]PERSON5[/TD]
[TD]PERSON6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]SS7100[/TD]
[TD]PERSON4[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]SS7100[/TD]
[TD]PERSON2[/TD]
[TD]PERSON4[/TD]
[TD]PERSON6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]SS7400[/TD]
[TD]PERSON2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]SS7200[/TD]
[TD]PERSON3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]SS7200[/TD]
[TD]PERSON1[/TD]
[TD]PERSON6[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


Table2
[TABLE="class: grid, width: 434"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]SS7100[/TD]
[TD]SS7200[/TD]
[TD]SS7300[/TD]
[TD]SS7400[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PERSON0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PERSON1[/TD]
[TD] [/TD]
[TD] ???[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]PERSON3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PERSON4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance!
Jen

 

[steve the fish]

Try this in I2:

=SUMPRODUCT(($B$2:$D$9=$H2)*($A$2:$A$9=I$1))

 

[Joe4]

Your ranges are not the same size. See: https://support.office.com/en-us/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
Note this important note:

[FONT=&quot]IMPORTANT:[/FONT][FONT=&quot] Each additional range must have the same number of rows and columns as the [/FONT][FONT=&quot]criteria_range1[/FONT][FONT=&quot] argument.[/FONT]

 

[angeloudaki]

Try this in I2:

=SUMPRODUCT(($B$2:$D$9=$H2)*($A$2:$A$9=I$1))

Perfect - thank you! Seems to work... Clearly using the wrong code. Will look that one up and add it to my list! :D

 

[angeloudaki]

Your ranges are not the same size. See: https://support.office.com/en-us/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
Note this important note:

Thanks Joe4 - I didn't know that!! Noted the link :D