Missing value indication in SUMPRODUCT

[Anbuselvam K]

Hi

As per the below image, I have used the SUMPRODUCT formula in the cells L3 to L13.

In the Second row some of the RM not having the prices in the first row (D, E, I, and J)

But, The formula sumproduct has given the results as usual.

I need to get the indications if the Products having Values in the RM and that RM doesn't have a price in row1.

Eg: Products 2 and 3 used RM E and F which doesn't have a price in row1.

So Products 2 and 3 need to indicate in the price/kg column L (By colour change or some other indication)



Thanks in advance.

 

[pecoflyer]

I can't begin to understand what your problem is
Please add manually calculated expected results. Thx

BTW, why the % in your formula. Also sumproduct already is an array formula. No reason to commit it with CSE

 

[Anbuselvam K]

Thanks for your reply.

Manual Example as below.

Product 2 used the RM Name of "A:D & F"

Except for RM D, all the RM have a price in row 1

Because of the missing RM price, the product cost is not correct in the cell "L4"

So it should be indicated with some difference than others.

 

[bosco_yip]

Try,




Select L3:L13 >> Conditional Formatting >> New Rule >> >> Use a formula…>> Rule formula :

=SUMPRODUCT(($B$1:$K$1="")*($B3:$K3<>""))>0

>> Click "Format" >> Choose cell back ground color (pink) >> OK

 

[Anbuselvam K]

Thanks a lot Bosco!

It is working perfectly.