Counting using criteria in different columns

[Dan Morris]

Hello MrExcel Members

Could you please help me with this.

I have a database in which:
Column E contains place names, ie: London, Cardiff, etc
Column G contains abbreviated departmental names, ie: EM, FEE, etc
Column P contains a number, ie: 1.0 or 0.4 or 0.6 etc

At the moment, I create a count depending on the place name value, using this equation:

=SUMPRODUCT(('TALENT SS'!$E$5:$E$369="LONDON")*ISNUMBER('TALENT SS'!$P$5:$P$369))

This equation then counts the numbers between P5 and P369 when E5-E369 contains "London"

I now need to complicate that by counting depending on what's in column E and in column G, so for example:
if E = London and G = EM, it will count the number in P (so in that example if E = London but G =FEE, then it wouldn't count it).

I hope I've managed to explain that as I'm trying to get my own head round it.

I'd be most grateful if anyone is able to help me.

Thank you so much. Dan

 

[jtakw]

Hi,

You just need to add that condition in your formula:

=SUMPRODUCT(('TALENT SS'!$E$5:$E$369="LONDON")*('TALENT SS'!$G$5:$G$369="EM")*ISNUMBER('TALENT SS'!$P$5:$P$369))

 

[Dan Morris]

Thank you so much for helping. I've copied that exactly and re-checked over and over that I'm using the right column names etc, but it doesn't seem to be presenting back the correct answer, it keeps giving me an answer of 10 when the correct answer is 16.5... I can't see how the equation isn't working.

 

[Joe4]

it keeps giving me an answer of 10 when the correct answer is 16.5... I can't see how the equation isn't working.

The formula cannot return a fraction like 16.5, as it is doing a count, and counts can only be whole numbers!

You are multiplying the TRUE/FALSE value of three conditions:
- Is column E equal to "LONDON"?
- Is column G equal to "EM"?
- Is column P a number?

If all three conditions are met, it will return a 1, otherwise it will return a 0.
Then it will add up all the 1's. So you can only end up with a whole number.

Are you really intending to add the amounts in column P (instead of just checking to see if they are a number)?
If so, then try:

[COLOR=#333333]=SUMPRODUCT(('TALENT SS'!$E$5:$E$369="LONDON")*('TALENT SS'!$G$5:$G$369="EM")*('TALENT SS'!$P$5:$P$369))[/COLOR]