FORMULA HELP any body

D

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
 
D

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
 
T

Toppers

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

Your target date is in A1

Assumes all "dates" are date format.
 
D

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 ?.
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula Help 2
formula help 5
FORMULA HELP 0
MONTH FORMULA 6
How to determine the value? 2
MONTH INCREMENT FORMULA 3
Pivot table 3
Calculating occupational sick pay entitlement 0

Top