Count values in a range based on multiple criteria

[Sue Robinson]

Hi Guys
I would like to count the number of X's in a column (G1:G10) based on matches in two other columns. For example I would like to know how many staff members from a particular hub (range A1:10) based on their location (range B1:B10) would like to attend Excel training. Staff belonging to a particular hub could be based in several different locations. So staff belonging to the Metro South Hub (A1:A10) could be based in either the Gold Coast, Meadowbrook or Bayside(B1:B10), but I only want to count those who have requested Excel training(G1:G10).

I am probably overthinking this, I have tried IF, COUNTIF, IF(AND), COUNTIF(AND) and several variations of the above and have suceeded in counting all the X's but can't count the the X's based on hub and location ...

And advice would be appreciated.

Cheers

Sue

 

[VoG]

Try like this

=SUMPRODUCT(--(A1:A10="hub1"),--(B1:B10="Bayside"),--(G1:G10="X"))

 

[Sue Robinson]

Thanks for the quick response, the solution worked a treat within the worksheet in question. Now I would like to put the solution in a different worksheet ... and have fiddled with it a bit, but am now coming up with a #VALUE! error ...

Anu idea's?

Cheers

Sue

 

[VoG]

Can you post the formula that fails.

 

[Sue Robinson]

<TABLE style="WIDTH: 1065pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1420><COLGROUP><COL style="WIDTH: 1065pt; mso-width-source: userset; mso-width-alt: 25965" width=1420><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 1065pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17 width=1420>=SUMPRODUCT(--('Statewide Data'!$A$4:$A$104="METRO SOUTH"),--('Statewide Data'!$B$4:$B$104="GOLD COAST"),--('Statewide Data'!G4:G101="X"))

</TD></TR></TBODY></TABLE>

 

[VoG]

All the ranges must be the same size. Try

=SUMPRODUCT(--('Statewide Data'!$A$4:$A$104="METRO SOUTH"),--('Statewide Data'!$B$4:$B$104="GOLD COAST"),--('Statewide Data'!G4:G104="X"))