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

 

[barry houdini]

Don't you have columns B and C the wrong way round? If your data is as you show it then I presume you need

=SUMPRODUCT(--(Sheet1!$A$1:$A$10="n"),--(Sheet1!$C$1:$C$10="y"),Sheet1!B1:B10)

this formula doesn't need to be array entered

 

[evanslee]

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

Thanks again

Lee

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.

When do i need to enter it as an array and when dont I ?

Thanks houdini, your a star!