SUMPRODUCT OR SUM(IF ?

evanslee

New Member
Joined
Sep 15, 2006
Messages
19
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 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
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top