SUMPRODUCT Formula with If & division

[jgarland]

I need a sumproduct formula as shown below. The formula will be in a different cell at the bottom of the sheet.

My formula is =IF(SUMPRODUCT(C2:C22="CLF"),SUMPRODUCT(B2:B22/100,BE2:BE22,B2:B22,BE23:BE22))

Basically, If column C contains CLF then divide column B by 100, times the value in column BE (99.95 total), otherwise if column C is blank then column BE times column B (total 9,995.00).

The result of this formula is FALSE, instead of the desired 99.95 or 9,995.00. I have altered this formula every way I can think of, plus researched the net, and I still can't make it work. Any help is greatly appreciated.

Edit: the screen is not showing correctly; Column A shows 99.95 (in red), column B shows 99.95, column C shows CLF, column BE shows 100.

A B C BE
olumn B

 

[mart37]

BE23:BE22 =>BE2:BE22

 

[mart37]

=SUMPRODUCT(((C2:C22="CLF")*(B2:B22/100)*(BE2:BE22))+((C2:C22<>"CLF")*(B2:B22)*(BE2:BE22)))

 

[jgarland]

Thanks for catching the typo!

Your formula returns #VALUE!. I did a copy and paste and verified the formula but it seems not to work.

Thoughts?

 

[JamesCanale]

I'm not sure if I understand your exact requirements, but:

=SUMPRODUCT(--(C2:C22="clf"),(B2:B22)/100,BE2:BE22)+SUMPRODUCT(--(C2:C22<>"clf"),B2:B22,BE2:BE22)

This is doing the following:
If the value in col C is 'CLF' then b/100*be
If col C is anything but "CLF" then b*be
And summed up from 2 to 22. But I'm not very sure this is what you want.

 

[jgarland]

Your description is correct, but it still returns #VALUE!.

 

[alz]

Your description is correct, but it still returns #VALUE!.

Check (B2:B22 ) columns . is there Space or "" which is invisible by formating.Text devide by number can cause #value err.

if there is no formula in range (B2:B22) .clear the cells which has no data.
U can check by istext(B2) and drag down.

 

[jgarland]

That seemed to be it! I made sure of the formatting and now your formula is working. Thank you so much for your help!

 

[jgarland]

In another area of my spreadsheet I have this same setup, except column B (cell B2) contains a formula that pulls in pricing from elsewhere. The solution you provided works just fine on an empty cell with user input of pricing, but not on a cell that contains a formula (the dreaded #VALUE!). I've tried to modify your solution but so far I can't get it to work. Any ideas?

 

[jgarland]

No worries, I got it sorted, all is good. Thanks again!