Cumulative sum on Condition (Horizontally)

[vijaydsk1970]

Being a novice in excel i am forced to post this question.

my data is like this

apr 05 apr 05 may 05 may 05 june 05 june 05 july 05 july 05
prod 1 prod 2 prod 1 prod 2 prod 1 prod 2 prod 1 prod 2
ro1 4 12 8 16 12 20 16 24
ro2 8 16 12 20 16 24 20 28
ro3 12 20 16 24 20 28 24 32

i want a formula which caliculates basing on condition if i give

d1= july 05
d2= prod 1
d3= ro1

the formula should caliculate cumulative from apr-05 to july-05 and give me
result as 40. Like wise if i change any thing in d1, d2 and d3 the value
should change accordingly.

(example 4+8+12+16=40)

thanks for your advice in advance.

 

[pshepard]

First, paste the following into a blank worksheet, into cell A1:

MM_YR,Apr 05,Apr 05,May 05,May 05,Jun 05,Jun 05,Jul 05,Jul 05
PROD,prod 1,prod 2,prod 1,prod 2,prod 1,prod 2,prod 1,prod 2
row1,4,12,8,16,12,20,16,24
row2,8,16,12,20,16,24,20,28
row3,12,20,16,24,20,28,24,32

data 1,May 05
data 2,prod 2
data 3,row3

Second, select cells A1:A9, select the Data menu, Text to Columns,
Delimited, Next, Comma, Next, Finish

Third, create names for names and their ranges below by selecting indicated
range for the name below; then select the Insert menu, Name, Define, copy and
paste the range name from the corresponding range in the list below into the
Name refers to box and verify that it matches the correct range.

Data =Sheet1!$B$3:$I$5
data_1 =Sheet1!$B$7
data_2 =Sheet1!$B$8
data_3 =Sheet1!$B$9
MM_YR =Sheet1!$B$1:$I$1
PROD =Sheet1!$B$2:$I$2
rownum =Sheet1!$A$3:$A$5

Last, enter the array formula below by copy and pasting the formula into a
cell, then PRESS and HOLD the Ctrl AND Shift buttons down WHILE pressing the
Enter button. When this is done correctly, Excel will add curly brackets {}
before and after the formula.

=SUM(IF((MM_YR<=data_1)*(PROD=data_2)*(rownum=data_3),data))

You can change the values in cells B7, B8 and B9 – and the result from this
formula will change based on your selections.

 

[Toppers]

For this example, the data table is in range A1:I5 and the paramters are in
N1 to N3 rather than D1 to D3.

I have also assumed the date fields to be date formatted as "mmm-yy" NOT
TEXT fields.


=SUMPRODUCT(($A$3:$A$5=N3)*($B$1:$I$1<=N1)*(B2:I2=N2)*($B$3:$I$5))

HTH

 

[vijaydsk1970]

Dear Toppers
Really a master piece this sorted my problems
thanks for your help.

 

[Toppers]

Thanks for feedback.

Dear Toppers
Really a master piece this sorted my problems
thanks for your help.

 

[vijaydsk1970]

can you give me your mail id pls
thanks

Thanks for feedback.