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

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

Any ideas on how to fix this?

C--Users-lfreund-Downloads-COST4.xlsm

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

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?

What is the desired behavior? Could you describe briefly the logic that you need?

ASKER

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.

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

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

ASKER

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?

to work without giving me a #VALUE! error?

ASKER