SUMPRODUCT Formula with If & division

jgarland

New Member
Joined
Nov 6, 2005
Messages
43
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
Capture.JPG
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,058
Office Version
  1. 2016
Platform
  1. Windows
=SUMPRODUCT(((C2:C22="CLF")*(B2:B22/100)*(BE2:BE22))+((C2:C22<>"CLF")*(B2:B22)*(BE2:BE22)))
 

jgarland

New Member
Joined
Nov 6, 2005
Messages
43
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
Joined
Jan 13, 2021
Messages
125
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 6, 2005
Messages
43
That seemed to be it! I made sure of the formatting and now your formula is working. Thank you so much for your help! (y)(y)(y)(y)(y)
 

jgarland

New Member
Joined
Nov 6, 2005
Messages
43
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,441
Messages
5,624,797
Members
416,054
Latest member
Ariel2219

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top