Link to home
Create AccountLog in
Avatar of LUIS FREUND
LUIS FREUND

asked on

Sumproduct not working on existing sheet

I'm adding up the cost that is less than the base cost.  On the attachment on column M is less than column H then I want to sum all that up.  The formula I'm using does not work for some reason and I'm getting a #value error.

At cell M693 I have this formula:  =SUMPRODUCT((M10:M688<H10:H688)*(M10:M688))

Any ideas on how to fix this?
C--Users-lfreund-Downloads-COST4.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Ogandos
Ogandos
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of LUIS FREUND
LUIS FREUND

ASKER

AWESOME!!!  Thank you!
I just noticed that it actually summed up everything in the column.  So it's not really working......
Hey Luis,

What is the desired behavior? Could you describe briefly the logic that you need?
I wanted to add up all the cost per row that is less than the baseline cost.  So in this case if the cost at column M is less than the cost of column H then add that up.  See below

Base Total Cost              SSVFC Cost
$1000                               $500
$5000                               $10000
$2000                               $1000

So  the value I want is $1500 ($500 + $1000) since those cost is less than the base cost.
Please review the uploaded file.

It is the same that you originally created, but just testing the logic to confirm that it is what you need. (Look a second sheet called "Test", located at the bottom of the spreadsheet). You can also validate it with additional values from rows 2 to 9.
C--Users-lfreund-Downloads-COST4--2.xlsm
Ah I see what is going on.....there is a formula at column M, how can I get this formula =SUMPRODUCT((M10:M688>H10:H688)*(M10:M688))

to work without giving me a #VALUE! error?