Cumulative Sum with conditions

[Shabeer]

Two set of data is below 1) "Data" worksheet & 2) "Formula" worksheet

"Data" worksheet (Information)

2010 and 2011 Quarter wise Sales, GP with Plan for 2 product types (soap and shampoo)

"Formula" sheet (To create formula)

To make a SUM formula under "YTD Sum formula" based on 3 conditions -

1) Product, QuarterYr will be pre-selected

2) Sum required is Cumulative

3) cumulative sum to be limited to particular year alone

Looking forward to all of your support to find a solution in getting YTD sum.


"Data" Sheet

Product Quarter Sales SalesPlan GP GPPlan

soap Q12010 $1,000 $1,200 22% 20%

soap Q22010 $3,000 $3,000 21% 20%

soap Q32010 $3,000 $4,000 22% 20%

soap Q42010 $2,000 $3,000 19% 20%

soap Q12011 $5,000 $1,500 22% 20%

soap Q22011 $3,000 $3,500 21% 20%

soap Q32011 $3,000 $4,500 19% 20%

soap Q42011 - $3,000 20%

shampoo Q12010 $100 $300 22% 20%

shampoo Q22010 $200 $400 21% 20%

shampoo Q32010 $300 $300 22% 20%

shampoo Q42010 $400 $400 19% 20%

shampoo Q12011 $500 $300 22% 20%

shampoo Q22011 $600 $700 21% 20%

shampoo Q32011 $700 $600 19% 20%

shampoo Q42011 - $700 20%


"Formula" Sheet

Product shampoo

Qtr,Yr Q32011


current qtr Year To Date

Metric Q32011 Sum Formula

Sales 700 If Product=shampoo, If Qtr,Yr=Q32011,then YTD =$500+$600

SalesPlan 600 =$300+$700

GP 19% can we get weighted average GP here?

GPPlan 20%


Thank You


Shabeer

 

[Luke M]

I'm not sure how the GP works, but here's the formula for sales current and YTD.

Current:

=SUMPRODUCT(--(Data!$A$2:$A$17=B1),--(Data!$B$2:$B$17=B2),Data!$C$2:$C$17)

YTD:

=SUMPRODUCT(--(Data!$A$2:$A$17=B1),--(MID(Data!$B$2:$B$17,2,1)<MID(B2,2,1)),--(RIGHT(Data!$B$2:$B$17,4)=RIGHT(B2,4)),Data!$C$2:$C$17)


Where B1 is "shampoo" and B2 is "Q32011". You can follow a similar pattern for the other metrics, if needed.

 

[Shabeer]

Thanks for the quick reply, looks like the formula is working! Great help.


Regards


Shabeer