sumproduct help

[Muthukrishnan V]

Excel 2007



Formula in S3:
=SUMPRODUCT(--
(D3:D20,D23:D47,D51:D67,D70:D74,D77:D124,D129:D140,D143:
D153,D156:D157=R3),P3:P20,P23:P47,P51:P67,P70:P74,P77:P12
4,P129:P140,P143:P153,P156:P157)

Column D contains Amount in numericals.

Column R3 is a Helper Cell containing name like KM in general

format.

Column P contains names like KM and so on.


With the above formula in S3, I get : #Value !.

Kindly help.

 

[jasonb75]

It will not work with ranges broken up like that, see if this works

=SUMPRODUCT(D3:D157,--(P3:P157=R3))

 

[Peter_SSs]

Your set-up is unclear to me as your attempted formula seems to indicate names in column D but your written description says they are in column P. I have assumed column P is the correct one for names with numbers in D.
Try this

=SUMPRODUCT(--(P3:P157=R3),LOOKUP(ROW(P3:P157),{3,21,23,48,51,68,70,75,77,125,129,141,143,154,156},{1,0,1,0,1,0,1,0,1,0,1,0,1,0,1}),D3:D157)


Edit:
.. or a little shorter

=SUMPRODUCT(--(P3:P157=R3),--ISODD(MATCH(ROW(P3:P157),{3,21,23,48,51,68,70,75,77,125,129,141,143,154,156})),D3:D157)

=SUMPRODUCT(--(P3:P157=R3),--MOD(MATCH(ROW(P3:P157),{3,21,23,48,51,68,70,75,77,125,129,141,143,154,156}),2),D3:D157)

 

[Muthukrishnan V]

Thank you Sirs

 

[Peter_SSs]

Thank you Sirs

You're very welcome.