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:

	A	B	C	D
1	Module	Staff 1	Staff 2	Staff 3
2	SS7100	PERSON1	PERSON2
3	SS7100	PERSON3	PERSON2
4	SS7300	PERSON3	PERSON5	PERSON6
5	SS7100	PERSON4
6	SS7100	PERSON2	PERSON4	PERSON6
7	SS7400	PERSON2
8	SS7200	PERSON3
9	SS7200	PERSON1	PERSON6

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

Table2

	H	I	J	K	L
1	Name	SS7100	SS7200	SS7300	SS7400
2	PERSON0
3	PERSON1		???
4	PERSON3
5	PERSON4

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>

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