SUMPRODUCT OR SUM(IF ?

[evanslee]

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

 

[infuse]

Is this what you want?
If not can you explain this part one more time? ('Sheet1'!$K$2:$K$3223 …..

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

Infuse

 

[barry houdini]

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]

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]

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.

=(A1>B1)*(B1<C1)

 

[evanslee]

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.

=(A1>B1)*(B1<C1)

 

[Brian from Maui]

Care to post the formula you are using?

 

[barry houdini]

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]

Im using this formula entered as an array

=SUMPRODUCT(--(Sheet1!$A$1:$A$10="n"),--(Sheet1!$B$1:$B$10="y"),Sheet1!C1:C10)

but the value is showing as 0

 

[evanslee]

=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