Help!!! formula mess

[arnoldmiranda]

Hi,
I somehow am not getting my result, which should be the sum of sheet1 og column J

=IF(SUMPRODUCT(--(Sheet1!B:B=Sheet2!B3),--(SUMPRODUCT(Sheet1!E:E<>Sheet2!A1))),SUM(Sheet1!J2:J10),"")

Appreciate some help

Thanks

 

[scottylad2]

Hi,
I somehow am not getting my result, which should be the sum of sheet1 og column J

=IF(SUMPRODUCT(--(Sheet1!B:B=Sheet2!B3),--(SUMPRODUCT(Sheet1!E:E<>Sheet2!A1))),SUM(Sheet1!J2:J10),"")

Appreciate some help

Thanks

This part will make an array of 1's & 0's for everything that matches whatever is in Sheet 2 B3 SUMPRODUCT(--(Sheet1!B:B=Sheet2!B3)

and the next part will do the same with everything that is nit = Sheet2 A1
SUMPRODUCT(Sheet1!E:E<>Sheet2!A1) and thereafter your Sumproduct stops. You have that as the start of your If statement but as is your Sumproduct isn't summing anything.....

you may be better trying to upload a small sample. Your If isn't actually comparing the Sumproduct to anything and therefore your next argument won't kick in

Try this

SUMPRODUCT(--(Sheet1!J2:J10=Sheet2!B3),--(Sheet1!E2:E10<>Sheet2!A1),Sheet1!J2:J10)) The arrays need to be the same size

 

[texasalynn]

in versions below Excel 2007 you can not use whole column references

 

[texasalynn]

so is this what you want??

=SUMPRODUCT(--(Sheet1!B2:B10=Sheet2!B3),--(Sheet1!E2:E10<>Sheet2!A1),(Sheet1!J2:J10))

 

[arnoldmiranda]

Hi,

=SUMPRODUCT(--(Sheet1!B2:B10=Sheet2!B3),--(Sheet1!E2:E10<>Sheet2!A1),(Sheet1!J2:J10))

Ths is exactly what I needed, thanks...I have another query though, based on the first two confitions can I use this formula to do a count as well?
eg: =SUMPRODUCT(--(Sheet1!B2:B10=Sheet2!B3),--(Sheet1!E2:E10<>Sheet2!A1), counta(Sheet1!J2:J10)) - obvoiuosly my formula doest work, but just wanted to give you an idea of what I was looking for....thanks

 

[Aladin Akyurek]

Hi,

=SUMPRODUCT(--(Sheet1!B2:B10=Sheet2!B3),--(Sheet1!E2:E10<>Sheet2!A1),(Sheet1!J2:J10))

Ths is exactly what I needed, thanks...I have another query though, based on the first two confitions can I use this formula to do a count as well?
eg: =SUMPRODUCT(--(Sheet1!B2:B10=Sheet2!B3),--(Sheet1!E2:E10<>Sheet2!A1), counta(Sheet1!J2:J10)) - obvoiuosly my formula doest work, but just wanted to give you an idea of what I was looking for....thanks

Your spec is ambiguous... It's probably one of the following:

=SUMPRODUCT(--(Sheet1!B2:B10=Sheet2!B3),--(Sheet1!E2:E10<>Sheet2!A1))

=SUMPRODUCT(--(Sheet1!B2:B10=Sheet2!B3),--(Sheet1!E2:E10<>Sheet2!A1),--ISNUMBER(Sheet1!J2:J10))

Or, elaborate a bit more...