Count all the values from 1 table if it's true that they exist in a nother table

[fructine]

Hi guys,

I need to figure out how to create 1 single formula that will allow me to count all the values given a couple of criteria, only if they exist in another sheet's table.
Here is what it looks like:

Sheet A: Total registered
Team ID Location
Anne 10 France
Mark 20 France
John 11 Italy
Rose 21 Italy
Peter 12 Germany
Hans 22 Germany
Elena 23 USA

Sheet B: Did not attend
Name ID Location
Peter 12 Germany
Alice 13 USA
Elena 23 USA

Need to solve in Sheet C: Failed attendance by location (ONLY if registered)
Location Failed Attendance
France ???
Italy ???
Germany ???
USA ???

How would you solve for this? Again I am looking for a single formula. I have come up with a solution that requires creating another table but I'm hoping to do without that.

Thanks

 

[Fluff]

You could use a countif,
assuming on sheet C "France is in A2, something like

=countif(sheetB!:C:C,A2)

In B2 copied down

 

[fructine]

You could use a countif,
assuming on sheet C "France is in A2, something like

=countif(sheetB!:C:C,A2)

In B2 copied down

I forgot to add the cell numbers ... sorry
The issue with this formula is that if we count for example how many from USA did not attend, it would return the value "2", which would be wrong because I only want to count the ones who 1) did not attend and 2) were registered. So the correct answer should be "1" because Alice did not attend but she did not register.
Any suggestion?

 

[Eric W]

Maybe:

Book4
	A	B	C	D	E	F	G	H	I	J
1	Team	ID	Location		Name	ID	Location		Location	Failed Attendance
2	Anne	10	France		Peter	12	Germany		France	0
3	Mark	20	France		Alice	13	USA		Italy	0
4	John	11	Italy		Elena	23	USA		Germany	1
5	Rose	21	Italy						USA	1
6	Peter	12	Germany
7	Hans	22	Germany
8	Elena	23	USA
9
Sheet5

Cell Formulas
Range	Formula
J2:J5	J2	=SUMPRODUCT(--($G$2:$G$10=I2),--(COUNTIFS($B$2:$B$20,$F$2:$F$10,$C$2:$C$20,I2)>0))

 

[fructine]

Maybe:

Book4
	A	B	C	D	E	F	G	H	I	J
1	Team	ID	Location		Name	ID	Location		Location	Failed Attendance
2	Anne	10	France		Peter	12	Germany		France	0
3	Mark	20	France		Alice	13	USA		Italy	0
4	John	11	Italy		Elena	23	USA		Germany	1
5	Rose	21	Italy						USA	1
6	Peter	12	Germany
7	Hans	22	Germany
8	Elena	23	USA
9
Sheet5

Cell Formulas
Range	Formula
J2:J5	J2	=SUMPRODUCT(--($G$2:$G$10=I2),--(COUNTIFS($B$2:$B$20,$F$2:$F$10,$C$2:$C$20,I2)>0))

This works great!! Thanks.