Hello Everybody
I have the data with sales volumes of each customerwise for a financial
year. I have designed a format that is having three excel sheets. In
"sheet1", i have created a button and in that all months (i.e., apr-2007,
may-2007, like wise upto mar-2008)
In "sheet3" having all customerwise and monthwise data.
Now what i require is if i select the month in the "sheet1" from the button,
the cummulative of the total months starting from (ex: if i select july-07,
it has to pick up the apr-07-jul-07 cumulative volumes). My data in "sheet1"
is like this

c.code c.name apr-07 may-07 june-07.........mar-08 total
1010 xxxxxx 10 20 10 15
55

HOPE U HAVE UNDERSTAND IT.
All ready i used "SUMPRODUCT" for c.code, but i'm not able to get
cummulative volume as i have mentioned above.

CAN ANY BODY HELP ME ?
THANKS INADVANCE

Assuming your data begins in column C and your target date is in H1, try
something similar to:

=SUM(OFFSET(Sheet3!$C5,0,0,1,MONTH(H1)))

--

Damon Longworth

2007 Excel / Access User Conference
London, England - Currently rescheduled
St. Louis, Missouri - Oct 24-26, 2007
www.ExcelUserConference.com/


"deepak bsg" wrote in message
...
Hello Everybody
I have the data with sales volumes of each customerwise for a financial
year. I have designed a format that is having three excel sheets. In
"sheet1", i have created a button and in that all months (i.e., apr-2007,
may-2007, like wise upto mar-2008)
In "sheet3" having all customerwise and monthwise data.
Now what i require is if i select the month in the "sheet1" from the button,
the cummulative of the total months starting from (ex: if i select july-07,
it has to pick up the apr-07-jul-07 cumulative volumes). My data in "sheet1"
is like this

c.code c.name apr-07 may-07 june-07.........mar-08 total
1010 xxxxxx 10 20 10 15
55

HOPE U HAVE UNDERSTAND IT.
All ready i used "SUMPRODUCT" for c.code, but i'm not able to get
cummulative volume as i have mentioned above.

CAN ANY BODY HELP ME ?
THANKS INADVANCE

=SUMPRODUCT(--(Sheet3!$C$1:$N$1<=A1),Sheet3!$C2:$N2)

Your target date is in A1

Assumes all "dates" are date format.

"Damon Longworth" wrote:

Assuming your data begins in column C and your target date is in H1, try
something similar to:

=SUM(OFFSET(Sheet3!$C5,0,0,1,MONTH(H1)))

--

Damon Longworth

2007 Excel / Access User Conference
London, England - Currently rescheduled
St. Louis, Missouri - Oct 24-26, 2007
www.ExcelUserConference.com/


"deepak bsg" wrote in message
...
Hello Everybody
I have the data with sales volumes of each customerwise for a financial
year. I have designed a format that is having three excel sheets. In
"sheet1", i have created a button and in that all months (i.e., apr-2007,
may-2007, like wise upto mar-2008)
In "sheet3" having all customerwise and monthwise data.
Now what i require is if i select the month in the "sheet1" from the button,
the cummulative of the total months starting from (ex: if i select july-07,
it has to pick up the apr-07-jul-07 cumulative volumes). My data in "sheet1"
is like this

c.code c.name apr-07 may-07 june-07.........mar-08 total
1010 xxxxxx 10 20 10 15
55

HOPE U HAVE UNDERSTAND IT.
All ready i used "SUMPRODUCT" for c.code, but i'm not able to get
cummulative volume as i have mentioned above.

CAN ANY BODY HELP ME ?
THANKS INADVANCE

Thankyou very much. It is working but i'm having three types of products
which every customer will take those products every month. When i'm writing
this formula it is not working correctly. Please can u check this.

=SUMPRODUCT(--(Sheet3!$C$1:$N$1<=A1),Sheet3!$C3:$N3),--(sheet3$C$2:$N$2)

should this formula work on a multiple criteria ?.

"Toppers" wrote:


=SUMPRODUCT(--(Sheet3!$C$1:$N$1<=A1),Sheet3!$C2:$N2)

Your target date is in A1

Assumes all "dates" are date format.

"Damon Longworth" wrote:

Assuming your data begins in column C and your target date is in H1, try
something similar to:

=SUM(OFFSET(Sheet3!$C5,0,0,1,MONTH(H1)))

--

Damon Longworth

2007 Excel / Access User Conference
London, England - Currently rescheduled
St. Louis, Missouri - Oct 24-26, 2007
www.ExcelUserConference.com/


"deepak bsg" wrote in message
...
Hello Everybody
I have the data with sales volumes of each customerwise for a financial
year. I have designed a format that is having three excel sheets. In
"sheet1", i have created a button and in that all months (i.e., apr-2007,
may-2007, like wise upto mar-2008)
In "sheet3" having all customerwise and monthwise data.
Now what i require is if i select the month in the "sheet1" from the button,
the cummulative of the total months starting from (ex: if i select july-07,
it has to pick up the apr-07-jul-07 cumulative volumes). My data in "sheet1"
is like this

c.code c.name apr-07 may-07 june-07.........mar-08 total
1010 xxxxxx 10 20 10 15
55

HOPE U HAVE UNDERSTAND IT.
All ready i used "SUMPRODUCT" for c.code, but i'm not able to get
cummulative volume as i have mentioned above.

CAN ANY BODY HELP ME ?
THANKS INADVANCE

=SUMPRODUCT((Sheet3!$C$1:$N$1<=A1)*(sheet3!$C$2:$N $3))

if you are trying to sum over several (2) products on rows 2 & 3


"deepak bsg" wrote:

Thankyou very much. It is working but i'm having three types of products
which every customer will take those products every month. When i'm writing
this formula it is not working correctly. Please can u check this.

=SUMPRODUCT(--(Sheet3!$C$1:$N$1<=A1),Sheet3!$C3:$N3),--(sheet3$C$2:$N$2)

should this formula work on a multiple criteria ?.

"Toppers" wrote:


=SUMPRODUCT(--(Sheet3!$C$1:$N$1<=A1),Sheet3!$C2:$N2)

Your target date is in A1

Assumes all "dates" are date format.

"Damon Longworth" wrote:

Assuming your data begins in column C and your target date is in H1, try
something similar to:

=SUM(OFFSET(Sheet3!$C5,0,0,1,MONTH(H1)))

--

Damon Longworth

2007 Excel / Access User Conference
London, England - Currently rescheduled
St. Louis, Missouri - Oct 24-26, 2007
www.ExcelUserConference.com/


"deepak bsg" wrote in message
...
Hello Everybody
I have the data with sales volumes of each customerwise for a financial
year. I have designed a format that is having three excel sheets. In
"sheet1", i have created a button and in that all months (i.e., apr-2007,
may-2007, like wise upto mar-2008)
In "sheet3" having all customerwise and monthwise data.
Now what i require is if i select the month in the "sheet1" from the button,
the cummulative of the total months starting from (ex: if i select july-07,
it has to pick up the apr-07-jul-07 cumulative volumes). My data in "sheet1"
is like this

c.code c.name apr-07 may-07 june-07.........mar-08 total
1010 xxxxxx 10 20 10 15
55

HOPE U HAVE UNDERSTAND IT.
All ready i used "SUMPRODUCT" for c.code, but i'm not able to get
cummulative volume as i have mentioned above.

CAN ANY BODY HELP ME ?
THANKS INADVANCE