# SUMPRODUCT Formula with If & division

#### jgarland

##### New Member
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

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### mart37

##### Well-known Member
BE23:BE22 =>BE2:BE22

#### mart37

##### Well-known Member
=SUMPRODUCT(((C2:C22="CLF")*(B2:B22/100)*(BE2:BE22))+((C2:C22<>"CLF")*(B2:B22)*(BE2:BE22)))

#### jgarland

##### New Member
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

##### Board Regular

I'm not sure if I understand your exact requirements, but:
Excel Formula:
``=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

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

#### alz

##### Board Regular

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

##### New Member
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

##### New Member
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

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

Replies
6
Views
216
Replies
9
Views
235
Replies
3
Views
158
Replies
5
Views
213
Replies
8
Views
313

1,127,354
Messages
5,624,200
Members
416,017
Latest member
moritz210

### 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.

### Which adblocker are you using?

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

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