Multi-conditional Count

hutchie

New Member
Joined
Jun 25, 2009
Messages
13
Hi

I am aware there are some other threads on this - but I have tried the formula suggested in those and they don't seem to work!

I have a spread sheet which lists a customer type in column B - the customer type is numeric - either 1, 2, 3, 4 or 5 and then within the columns F to L there may or may not be a score from 1 - 5 entered.

I want to be able count how many of customer type 1 has placed a score within the range of columns F to L

I had tried

=SUMPRODUCT(--(B7:B37=1),--(F7:L37="1")) not fully understanding sum product in the hope that the formula would only count those 1's within the Range F7:L37 if the number entered within the range B7:B37 was 1.

This doesn't seem to have worked!

Ideally I would use a pivot table but as the scores are spread across several columns the table sprawls out and is difficult to read - I also need to replicate this over a number of worksheets so have found to paste tables of formulae is easier.

Hope you can help!

Thanks
Hutchie
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

vw412

Board Regular
Joined
Dec 16, 2011
Messages
182
If you simple want a count of each customer type and the value in the cells in F-L is the customer type then use this:

=CountIF($F$2:$L$25,"=1") for customer type 1

This will give you a count of cells that have the value 1 in the range F2:L25.
 

hutchie

New Member
Joined
Jun 25, 2009
Messages
13
Hi thanks for that - it is like a "countif" I want to do but with an additional condition in that I only want to count the number of 1, 2, 3, and 4's in F2 - L25 if column B is customer type 1 and then again for customer type 2 etc.

So that I can get a table saying

Customer Type 1 - there are 3 ratings of 1; there are 5 ratings of 2; there are 18 ratings of 3 etc. within the range F2:L25
Customer Type 2 - there are 3 ratings of 1; there are 5 ratings of 2; there are 18 ratings of 3 etc. within the range F2:L25
Customer Type 3 - there are 3 ratings of 1; there are 5 ratings of 2; there are 18 ratings of 3 etc. within the range F2:L25
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
I have a spread sheet which lists a customer type in column B - the customer type is numeric - either 1, 2, 3, 4 or 5 and then within the columns F to L there may or may not be a score from 1 - 5 entered.

I want to be able count how many of customer type 1 has placed a score within the range of columns F to L
Assuming that your values in F7:L37 are numeric, then why not add a column to sum all these values for each row?
For example, in cell M7, enter the formula:
=SUM(F7:L7)
and copy all the way down to L37.

Then you could use the SUMPRODUCT formula:
Code:
=SUMPRODUCT(--(B7:B37=1),--(M7:M37>0))
 

BenMiller

Board RegularThe ONLY cool kid on the block
Joined
Nov 17, 2011
Messages
1,959
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Between columns F:L, can one row have more than one score? Can those be different (from each other)?
 

vw412

Board Regular
Joined
Dec 16, 2011
Messages
182
Do you have a range defined in your workbook to put this table of results? Or do you want to see it as you show above?
 

vw412

Board Regular
Joined
Dec 16, 2011
Messages
182

ADVERTISEMENT

What versions of Excel are you working with?
 

hutchie

New Member
Joined
Jun 25, 2009
Messages
13
Hi

Excel 2010

I've just realised that it might not actually be possible - as the formula would need to check for example B2 and register the customer type and then check range F2:L2 for the count of scores within that range.

I don't have a range defined in the workbook for the table of results.

I think I am going to have to sort by customer type and then do a countif on the range - I was trying to avoid this as when it is sorted by another variable all the counts will change, but it is the only way I can think of doing it.

Thanks for the help so far!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
I've just realised that it might not actually be possible - as the formula would need to check for example B2 and register the customer type and then check range F2:L2 for the count of scores within that range.

I don't have a range defined in the workbook for the table of results.
I am not sure I understand what you are saying the problem is.

Did you take a look at the suggestion I provided?
 

vw412

Board Regular
Joined
Dec 16, 2011
Messages
182
Can you upload a sanitized version of this file so we could see what you are trying to do in context? I am not so sure you are right about not being possible. I don't think we fully understand the input and the desired output. A look at the workbook would help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,601
Members
414,081
Latest member
penguin23

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