#value error on sumproduct formula

Question

I have a #value error on a sumproduct formula. I used this formula and it works fine  =SUMPRODUCT(($C$4:$AK$4=C5:AK5)*(C6:AK6)) but when I want to turn it around and use it with column instead of rows I get the error. In my new formula the $c$4:$ak$4 is now c7:c37  the c5:ak5 is now d7:d37 and the c6:ak6 is now d8:d38 but it will not add up the values of my cell d8 if d7=c7. I can post the file to skydrive if needed. I am skipping every other cell if this is making it not work. Example c7,c9,c11 same with the d7,d9,d11 and d8,d10,d12 etc.  Thanks Karl

Answer 1

Check this one...

=SUMPRODUCT((C7:C37=D7:D37)*1,(D8:D38))

Answer 2

This did not work

Answer 3

Ok I have cell d5 and d6 as my result cells. This formula is in d5 and works fine                       =SUMPRODUCT(--($C$7:$C$37=D7:D37)) my cell d6 I want to show the results 0f d8 if cell c7=d7. Where i am skipping rows I mean d5 has the results if c7=d7 c9=d9 c11=d11 and so on every other row then i want in d6 to show value of d8 (example 10) if c7=d7   d10 if c9=d9 (example 12) so d6 then would show 22 cause d8 and d10 were = to c7=d7 and c9=d9.  now if d12 was not = to c11=d11 then nothing would be added to d6 now. Thanks  Karl

Answer 4

Try this...

=SUMPRODUCT(ISNUMBER(D7:D37)*(C7:C37=D7:D37),(D7:D37))

Answer 5

EDITED: I had a typo in my formula that made me think your adjusted formula was incorrect.

If you're using this, it should work:

=SUMPRODUCT((C7:C37=D7:D37)*(D8:D38))

Second, you mentioned that you want to skip every other cell. Can you clarify what you mean by that? There are some special techniques we use that only reference every 2, or 3, or whatever cells.