Counting using criteria in different columns

Dan Morris

New Member
Joined
May 22, 2012
Messages
34
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,426
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
May 22, 2012
Messages
34
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
[COLOR=#333333]=SUMPRODUCT(('TALENT SS'!$E$5:$E$369="LONDON")*('TALENT SS'!$G$5:$G$369="EM")*('TALENT SS'!$P$5:$P$369))[/COLOR]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,976
Messages
5,599,139
Members
414,292
Latest member
kingshuk963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top