Is the Nested IF Statement the correct way forward?

[MJTownend1983]

Hi,

I am trying to find a single formula that allows me to recognize whether a company has the following;

• Has multiple products in both Product Ranges
• Has single products in both Product Ranges
• Has multiple products in a single Product Range
• Has a single product in a single Product Range

I thought a nested if statement could work, buy can't get it to work with the 10 different products.

Data is as follows;
[TABLE="width: 1122"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5, align: center"]PRODUCT RANGE 1[/TD]
[TD="colspan: 5, align: center"]PRODUCT RANGE 2[/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]Single or Multiple[/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 3[/TD]
[TD]Product 4[/TD]
[TD]Product 5[/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 3[/TD]
[TD]Product 4[/TD]
[TD]Product 5[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be much appreciated.

Thanks,
Matt

 

[Joe4]

Welcome to the Board!

Take a look at the COUNTIF function, where you can count the number of entries greater than zero.
See: https://exceljet.net/excel-functions/excel-countif-function

 

[Special-K99]

Here's a start

Assume Company A is in A3
then

=5-COUNTIF(C3:G3,0) returns number of products in that range 1
=5-COUNTIF(H3:L3,0) returns number of produicts in that range 2

I'm thinking a table based on those two results or a LOOKUP maybe

Still thinking...