Sum Columns

[Soniya]

Hi All,

I have the Following in my worksheet

A1 contains a month end date for eg. 30-04-05

B2 C2 ..... AK2 AM2
31-1-03 28-2-03 ..... 31-12-05 TOTAL
100 250 ..... 425 xxxx
250 150 ..... 200 xxxx

I want to get the total based on the date in my A1. For eg if A1
containd 31-3-04 in my AM3 the total shud be sum(B33) (31-01-03 to
31-03-04)

so when ever I change the date in my A1 my total column shud take from
31-1-03 up to the column in which the same date appears.

Is there an easy way to acheive this either thru code or formulae?

thanks

 

[Bob Phillips]

=SUM(OFFSET($B3,,,,MATCH($A$1,2:2,0)-1))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[keepITcool]

Hi Soniya!

first:
use a cell with a formula to find the the date in row2
B1> =MATCH(a1,2:2,0)

then:
you need indirect or offset function.
the offset function has 5 arguments

range,moveR,moveC,height,width

let's set the 'starting range' at b3.
we're not "moving" it, just "sizing" so argument 2/3 will be zero.
b1 holds position so we must create the width by deducting 1

AM3> =SUM(OFFSET($B3,0,0,1,$B$1-1)

try it..

....

got it?

now it's also easy to adapt the formulas
so you can sum from/to.

you'll need a cell with the From position.
you'll need a cell with the Thru position.

assume a1 is date from
assume b1 is date thru
c1 (position from) =MATCH(a1,2:2,0)
d1 (position thru) =MATCH(b1,2:2,0)

am3 => =SUM(OFFSET($A3,0,$C$1,1,$D$1-$C$1+1)

(i hope i got all the plus/minus 1 correct..
as i'm typing "off the cuff")




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Soniya wrote :

 

[Soniya]

Fantastic.. and worked well.... thanks for all the reply..

thanks again