sumproduct

[kl99ny]

Hi,

If someone could help me out with this, it is greatly appreciated.

Debbie Debbie 5 15
Karen Debbie 6 35
Jimmy Karen 7 15
Karen 8 #N/A
Debbie 10 #N/A
Jimmy 5 #N/A

So, this is what I want using sumproduct. Using Column A, lookup Column C. If there is a number in Column E then sumproduct Column D. If it is an #N/A in Column E, do not sumproduct Column D. I have typed up this =SUMPRODUCT(--(A4=$C$4:$C$9)*($E$4:$E$9>0),$D$4:$D$9). It would work if the #N/A's in Column E were zeros.

Thank you.

[Pete_UK]

Try it like this:

=SUMPRODUCT((A4=$C$4:$C$9)*($E$4:$E$9>0)*(isnumber($E$4:$E$9)),$D$4:$D$9)

Hope this helps.

Pete

[kl99ny]

No, it didn't work. I even tried to change e4:e9 to number format.

[Pete_UK]

Sorry, I should have removed the middle term. Try this:

=SUMPRODUCT((A4=$C$4:$C$9)*(isnumber($E$4:$E$9)),$D$4:$D$9)

Hope this helps.

Pete

[kl99ny]

Great! It works. Thank you very much.