Multi-conditional Count

[hutchie]

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

 

[vw412]

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]

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]

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:

=SUMPRODUCT(--(B7:B37=1),--(M7:M37>0))

 

[BenMiller]

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

 

[vw412]

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]

What versions of Excel are you working with?

 

[hutchie]

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]

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]

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.