So, it would appear that the data has no title/names against
the data sets
That makes it more difficult to use a matching facility such
as the = in
sumproduct, that would be appropriate to compare the row, or
in this case
the number of the row() against the month to be summed up
to
row 1 contains branch 1 values
row 2 contains branch 1 values
row 3 contains branch 1 values
column 1 is month 1 data through to column 12 being month 12
data
First - you need a facility to match the branchcode in a11
to the data -
perhaps
=MATCH(A11,{120;130;140},0)
for the data in rows 1, 2 and 3 being for branch codes of
120 130 and 140
respectively
Now the process is to use column number to limit the data to
be summed
As in find the column that matches the last month to be
included
=COLUMN(A1:L3)=$A$10)
Combining those, and the data from the portion of the full
range that
matches the criteria - rejoin the following into 1 line and
lose the extra
spaces
=SUMPRODUCT
(
(ROW(A1:L3)=
MATCH($A$11,{120;130;140},0)
)
*
(
COLUMN(A1:L3)<=$A$10
)
*
A1:L3
)
JimB
----- Original Message -----
From: "Tony Gee" <Tony.Gee KERRYLOGISTICS.CO.UK>
To: <EXCEL-GPEACH.EASE.LSOFT.COM>
Sent: Monday, April 23, 2007 9:44 AM
Subject: Re: YTD 'With a Twist'
> Dear all,
>
> Thanks for the many very useful(laurent, Dave
Damon,Bill) responses and
> other material.
>
> James,
> You may have something here (which I can use
elsewhere)
> To be more explicit the data (monthly) is contained in
a 3(branch) x
> 12(months) matrix (let's say range A1:L3). The
variables (month & branch)
> are integers contained in two separate cells lets say
A10 & A11. To get
> the monthly value I can use INDEX (which is supported)
but how can the
> Y-T-D figures be calculated using SUMPRODUCT?
>
> Thanks
> Tony
>
> Tony Gee
> Financial Controller
>
> Kerry Logistics (UK) Ltd
> Head Office
>
> Tel : +44 (0) 161 873 1403
> Fax : +44 (0) 161 873 8565
> Email : tony.geekerrylogistics.co.uk
>
> www.kerrylogistics.co.uk
> Empowering supply chains...China, Asia and beyond....
>
>
>
> James Button <jamesbuttonBLUEYONDER.CO.UK>
> Sent by: MS Excel General Q & A List
<EXCEL-GPEACH.EASE.LSOFT.COM>
> 20/04/2007 17:51
> Please respond to
> MS Excel General Q & A List <EXCEL-GPEACH.EASE.LSOFT.COM>
>
>
> To
> EXCEL-GPEACH.EASE.LSOFT.COM
> cc
>
> Subject
> Re: YTD 'With a Twist'
>
>
>
>
>
>
> A more explicit description of the data would help with
the provision of
> an
> appropriate formula, as would an indication as to how
the required
> selection
> is indicated by the user.
> However, perhaps something like
>
sumproduct(branch=$selection*month<=$selection*value:rang
e)
>
> JimB
>
>
> ----- Original Message -----
> From: "Tony Gee" <Tony.GeeKERRYLOGISTICS.CO.UK>
> To: <EXCEL-GPEACH.EASE.LSOFT.COM>
> Sent: Friday, April 20, 2007 4:08 PM
> Subject: YTD 'With a Twist'
>
>
> > Good afternoon to you all.
> >
> > I need to calculate YTD values based on two
variables contained in two
> > separate cells. The variables are the month and
branch. The data is
> > contained in a 3 x 12 range. Here's the twist:-
> >
> > I'm using a product called Xlcelcius (Dashboard
type tool) in
> conjunction
> > with Excel and there are certain functions not
supported including:-
> >
> > CHOOSE & OFFSET!
> >
> > Q.How can I calculate the YTD figures?
> >
> >
> > Thanks
> > Tony
> >
> > Tony Gee
> > Financial Controller
> >
> > Kerry Logistics (UK) Ltd
> > Head Office
> >
> > Tel : +44 (0) 161 873 1403
> > Fax : +44 (0) 161 873 8565
> > Email : tony.geekerrylogistics.co.uk
> >
> > www.kerrylogistics.co.uk
> > Empowering supply chains...China, Asia and
beyond....
> >
> > * Standard Terms and Conditions *
> > "All business is undertaken subject to the
applicable Standard Trading
> Terms
> > and Conditions and whatsoever agreements. COPIES
OF THE APPLICABLE
> STANDARD
> > TRADING TERMS AND CONDITIONS CAN BE OBTAINED UPON
REQUEST OR AT
> > WWW.KERRYLOGISTICS.COM."
> >
> > * Disclaimer *
> > This message and any attachments are confidential
and are intended only
> for the
> > use of the addressee. If you are not the intended
recipient, you should
> not
> > disseminate, distribute or copy this
communication. If you have received
> this
> > communication in error, please notify us
immediately by return email and
> delete
> > the original message. We use virus scanning
software but exclude all
> liability
> > for viruses or similar in any attachment. Thank
you.
> >
> >
>
------------------------------------------------------------
--------------
> > The EXCEL-G list is hosted on a Windows NT(TM)
machine running L-Soft
> > international's LISTSERV(R) software. For
subscription/signoff info
> > and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
> > COPYRIGHT INFO:
> > http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
> >
> >
>
>
------------------------------------------------------------
--------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
> international's LISTSERV(R) software. For
subscription/signoff info
> and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
> COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>
>
> * Standard Terms and Conditions *
> "All business is undertaken subject to the
applicable Standard Trading
Terms
> and Conditions and whatsoever agreements. COPIES OF
THE APPLICABLE
STANDARD
> TRADING TERMS AND CONDITIONS CAN BE OBTAINED UPON
REQUEST OR AT
> WWW.KERRYLOGISTICS.COM."
>
> * Disclaimer *
> This message and any attachments are confidential and
are intended only
for the
> use of the addressee. If you are not the intended
recipient, you should
not
> disseminate, distribute or copy this communication. If
you have received
this
> communication in error, please notify us immediately by
return email and
delete
> the original message. We use virus scanning software
but exclude all
liability
> for viruses or similar in any attachment. Thank you.
>
>
------------------------------------------------------------
--------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
> international's LISTSERV(R) software. For
subscription/signoff info
> and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
> COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|
