# SUMPRODUCT OR SUM(IF ?

#### evanslee

##### New Member
Ok, heres what i need to do...
Similar too...

=SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=B5),--('Sheet1'!\$K\$2:\$K\$3223<B6))

but, i want to
• SUM(Sheet2!E2:E3223)
but only where it matches
• ('Sheet1'!\$K\$2:\$K\$3223>=B5),--('Sheet1'!\$K\$2:\$K\$3223<B6)

Should I be using some kind of SUM(IF statement ?

Regards,
Lee

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Is this what you want?
If not can you explain this part one more time? ('Sheet1'!\$K\$2:\$K\$3223 …..
Code:
``SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=B5),(Sheet2!E2:E3223))``

Infuse

Ok, heres what i need to do...
Similar too...

=SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=B5),--('Sheet1'!\$K\$2:\$K\$3223< B6))

but, i want to
• SUM(Sheet2!E2:E3223)
but only where it matches
• ('Sheet1'!\$K\$2:\$K\$3223>=B5),--('Sheet1'!\$K\$2:\$K\$3223< B6)

Should I be using some kind of SUM(IF statement ?

Regards,
Lee

Try this

=SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=B5),--('Sheet1'!\$K\$2:\$K\$3223< B6),'Sheet2'!E2:E3223)

No, I need to look 3 colums that vary in length,

#ABC
1128
2124
3114
4126

I.e I want to sum(C1:C4) but only where it meets the criteria A1:A4=1 , and B1:B4=2

Im not sure if that makes it any easier, but SUMPRODUCT alone does not work, that does not SUM the amounts for me in column C (as in my ickle table example)

Can i do SUMPRODUCT(SUM(IF
but i only want one column to be summed if it meets the other two criteria in the array.

Regards,
Lee

Lee,

Did you try my suggestion. It sums sheet 2 column E where your other two criteria are met? Isn't that what you want?

infuse

Mr Excel Board doesn't like < and > signs. In some circumstances they are interpreted at HTML tags and you lose the text. You can see what was posted if you quote it.

To avoid the problem put spaces before and after < and > signs or use the code tags, e.g.

Code:
``=(A1>B1)*(B1<C1)``

I get an error, #VALUE!

Lee,

Did you try my suggestion. It sums sheet 2 column E where your other two criteria are met? Isn't that what you want?

infuse

Mr Excel Board doesn't like < and > signs. In some circumstances they are interpreted at HTML tags and you lose the text. You can see what was posted if you quote it.

To avoid the problem put spaces before and after < and > signs or use the code tags, e.g.

Code:
``=(A1>B1)*(B1<C1)``

Care to post the formula you are using?

You said that the columns vary in length, you need to use the same length ranges for sumproduct to work.

Assuming you do that then another cause of errors would be errors within your ranges.

If neither of these is the culprit then post the excat formula you're using

Im using this formula entered as an array
Code:
``=SUMPRODUCT(--(Sheet1!\$A\$1:\$A\$10="n"),--(Sheet1!\$B\$1:\$B\$10="y"),Sheet1!C1:C10)``

but the value is showing as 0

Code:
``=SUMPRODUCT(SUM(IF(--(Sheet1!\$A\$1:\$A\$10="n"),--(Sheet1!\$B\$1:\$B\$10="y"),Sheet1!C1:C10)))``

arghhhhh, I had the reference wrong, what a stupid mistake. sorry to all, you had it correct all along ! thanks so much, I have a red face now.

Thanks again

Lee

Replies
10
Views
339
Replies
6
Views
70
Replies
0
Views
235
Replies
4
Views
263
Replies
6
Views
599

1,217,382
Messages
6,136,239
Members
450,000
Latest member
jgp19

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