Sum average or other lookup help

[ndenaro]

Hello,

I am trying to do a lookup based on multiple conditions, and I had been using a SUMPRODUCT formula to do this.

Please Login or Register  to view this content.

However, I have recently come to realize that in the Mags-Unit-Hour sheet that I am using as a source, there are some duplicates. So the SUMPRODCUT is doing what it is designed to do and summing the values of these duplicates, however this is not what I want. Is there any way to return say an average of all the values that match this criteria?

If not what would be a lookup formula I could use to simply return the first instance rather than a sum of duplicates?

Thanks for any help.

[martindwilson]

divide the result by the count
so basicaly
sumproduct((--(a1:a5=d1),--(b1:b5=e1),(c1:c5))/sumproduct((--(a1:a5=d1),--(b1:b5=e1))
or to find first value
=INDEX($C$1:$C$5, MATCH(D1 & E1, INDEX(A1:A5 & B1:B5, 0), 0))

[ndenaro]

Thanks this is working well. One more question. Is there any way I can get this formula to disregard values of 0 or 99? The zero's and 99's are entered into the table as sort of error or missing values, so they are messing up my average and they are not needed there.

[ndenaro]

I should probably mention I am using your first method with the two sumproduct formulas

[martindwilson]

just add them in
=SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1),--(C1:C5<>0),--(C1:C5<>99),(C1:C5))/SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1),--(C1:C5<>0),--(C1:C5<>99))

[ndenaro]

Thank you again, one last thing should do it. What would I add in to also disregard blank cells? I tried setting C:C > 0 instead of <> 0, but that doesn't seem to do the trick.

[martindwilson]

c:c<>"" should do it

[ndenaro]

Great. I should have had that one myself. Thanks so much for all of your help it seems to be working well!