FORMULA HELP any body

[deepak bsg]

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

 

[Damon Longworth]

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/


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

 

[Toppers]

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

Your target date is in A1

Assumes all "dates" are date format.

 

[deepak bsg]

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]

=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