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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

infuse

Board Regular
Joined
Dec 7, 2005
Messages
126
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
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

evanslee

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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)
 

evanslee

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

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

Care to post the formula you are using?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

evanslee

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

evanslee

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

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,371
Members
410,911
Latest member
AniEx
Top