sumproduct issue

[jw01]

hello

having some trouble trying to sum for a YTD figure.

i am trying to obtain P7 YTD figure for various listing.

i am using the following sumproduct formula but getting a VALUE error:

=SUMPRODUCT(--(Total!$A$9:$A$128=Calc!$B4),--(Total!$D$8:$P$8<=Calc!$G$3),(Total!$D$9:$P$128))

essentially i want to sum by "listing" and find out the P7 YTD (i.e. P1+P2+P3+P4+...P7) total

the P1 etc means...jan, feb, march


can someone pls help??

[Ace_XL]

Dont you actually need just since none of your listings will be repeated..

=INDEX(Total!$A:$P,MATCH(B4,Total!$A:$A,0),MATCH($G$3,Total!$8:$8,0))

[jw01]

hey pal

i need P1-P7 total

since it a "year to date" figure....i need everything from P1-P7 as a total...not just P7

any thoughts?

[Ace_XL]

Try this instead..

=SUM(OFFSET(Total!$A$1,MATCH(B4,Total!$A:$A,0)-1,0,1,MATCH($G$3,Total!$8:$8,0)))