SUMIF accross multiple columns
 

I am trying to Sum a Year to date range based on a month reference. Ideas?

Sheet 1:

Jan05 Feb05 Mar05
12345 12 34 56
67890 78 90 12
23456 34 56 78

Sheet 2:
Column 1 Column2
12345 =SUMIF(CustNum,CustNum2,INDIRECT(Month))

Column3 (Not working)
=SUMIF(CustNum,CustNum2,INDIRECT(CONCATENATE(Month ,"Total")))

Perhaps give this a try

Assume
In Sheet1, A1:D4
-----------------------
Jan05 Feb05 Mar05
12345 12 34 56
67890 78 90 12
23456 34 56 78

and in Sheet2, you have

--------- Feb05
12345
67890
23456

where B1 will contain the required Year-to-date month to cumulate, viz.: If
the input in B1 is for example: Feb05, it means cumulate Jan05 & Feb05 figs

Put in B2:

=SUM(OFFSET(Sheet1!$B$1:$M$1,MATCH($A2,Sheet1!$A:$ A,0)-1,,,MATCH(B$1,Sheet1!
$B$1:$M$1,0)))

Copy B2 down to B4

This will return:

--------- Feb-05
12345 46
67890 168
23456 90

Changing B1 to: Mar05
yields

--------- Mar-05
12345 102
67890 180
23456 168

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"X" wrote in message
...
I am trying to Sum a Year to date range based on a month reference.
Ideas?

Sheet 1:

Jan05 Feb05 Mar05
12345 12 34 56
67890 78 90 12
23456 34 56 78

Sheet 2:
Column 1 Column2
12345 =SUMIF(CustNum,CustNum2,INDIRECT(Month))

Column3 (Not working)
=SUMIF(CustNum,CustNum2,INDIRECT(CONCATENATE(Month ,"Total")))

A slight revision to the formula. Try instead in B2:
=SUM(OFFSET(Sheet1!$B$1,MATCH($A2,Sheet1!$A:$A,0)-1,,,MATCH(B$1,Sheet1!$1:$1
,0)-1))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi

i'm not really sure what you're trying to achieve here ... if MONTH is a
range name it is not a good one ... suggest changing it for a start.

Secondly, i think an index, match formula might work best for the first
formula
=INDEX(Sheet1!A4:E7,MATCH(B1,Sheet1!A4:A7,0),MATCH (A1,Sheet1!A4:E4,0))
where A1 holds the month and B1 holds the customer number

and the following formula for the second
=SUM(OFFSET(Sheet1!A4,MATCH(B1,Sheet1!A5:A7,0),1,1 ,MATCH(A1,Sheet1!B4:E4,0)))
where A1 holds the month and B1 holds the customer number

Hope this helps
Cheers
JulieD


"X" wrote in message
...
I am trying to Sum a Year to date range based on a month reference. Ideas?

Sheet 1:

Jan05 Feb05 Mar05
12345 12 34 56
67890 78 90 12
23456 34 56 78

Sheet 2:
Column 1 Column2
12345 =SUMIF(CustNum,CustNum2,INDIRECT(Month))

Column3 (Not working)
=SUMIF(CustNum,CustNum2,INDIRECT(CONCATENATE(Month ,"Total")))