Hi,

If I have the following schedule (on bottom), I have another cell to input
numbers (1-12), Is there a formula could automatically summary each function
(sales, mkt etc) YTD when I update the input number.

I tried sumif and indirect functions, but sumif can only take on column.

i.e. if I input 3 (for March)

I should see:

Sales: 12, Mkt:16, admin: 11




Jan Feb Mar Apr ... Dec
Sales 1 5 6 8 11
Mkt 3 5 8 0 2
Adm 1 4 6 7 1

Thanks,

Glenn

Source table as posted assumed in A1:F4
Input placed in B7: 3
In B8: =SUM(OFFSET(B2,,,,B$7))
Copy B8 down to B10 to return required results for Sales, Mkt, Adm
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"zy1972" wrote:
If I have the following schedule (on bottom), I have another cell to input
numbers (1-12), Is there a formula could automatically summary each function
(sales, mkt etc) YTD when I update the input number.

I tried sumif and indirect functions, but sumif can only take on column.

i.e. if I input 3 (for March)

I should see:

Sales: 12, Mkt:16, admin: 11




Jan Feb Mar Apr ... Dec
Sales 1 5 6 8 11
Mkt 3 5 8 0 2
Adm 1 4 6 7 1

Thanks,

Glenn

suppose u have like this, Instead you 3 for march (use mar )

A B C D
1 Jan Feb Mar
2 sales 1 5 6
3 Mkt 3 5 8
4 Adm 1 4 7
5
6


In A6 input as jan or feb or mar

in A7 put this formula
="sales"&" "&SUM(OFFSET(B2,0,0,1,MATCH($A$8,B1:E1,0)))&","&"m kt"&"
"&SUM(OFFSET(B3,0,0,1,MATCH($A$8,B1:E1,0)))&", "& "adm"&"
"&SUM(OFFSET(B4,0,0,1,MATCH($A$8,B1:E1,0)))





On May 30, 7:30*pm, zy1972 wrote:
Hi,

If I have the following schedule (on bottom), I have another cell to input
numbers (1-12), Is there a formula could automatically summary each function
(sales, mkt etc) YTD when I update the input number.

I tried sumif and indirect functions, but sumif can only take on column.

i.e. if I input 3 (for March)

I should see:

Sales: 12, Mkt:16, admin: 11

* * * * * *Jan Feb Mar Apr ... Dec
Sales * 1 * *5 * * 6 * *8 * * * * 11
Mkt * * *3 * 5 * * *8 * *0 * * * * 2
Adm * *1 * 4 * * *6 * * 7 * * * *1

Thanks,

Glenn

sorry, my base file is more complicated than the one I posted before. it has
multi sales, Then How can I use one formula to summary YTD sales:

Jan Feb Mar Apr ... Dec
Sales-1 1 5 6 8 11
Mkt -1 3 5 8 0 2
Sales-2 1 4 6 7 1
Sales-3 2 4 7 8 12


"muddan madhu" wrote:

suppose u have like this, Instead you 3 for march (use mar )

A B C D
1 Jan Feb Mar
2 sales 1 5 6
3 Mkt 3 5 8
4 Adm 1 4 7
5
6


In A6 input as jan or feb or mar

in A7 put this formula
="sales"&" "&SUM(OFFSET(B2,0,0,1,MATCH($A$8,B1:E1,0)))&","&"m kt"&"
"&SUM(OFFSET(B3,0,0,1,MATCH($A$8,B1:E1,0)))&", "& "adm"&"
"&SUM(OFFSET(B4,0,0,1,MATCH($A$8,B1:E1,0)))





On May 30, 7:30 pm, zy1972 wrote:
Hi,

If I have the following schedule (on bottom), I have another cell to input
numbers (1-12), Is there a formula could automatically summary each function
(sales, mkt etc) YTD when I update the input number.

I tried sumif and indirect functions, but sumif can only take on column.

i.e. if I input 3 (for March)

I should see:

Sales: 12, Mkt:16, admin: 11

Jan Feb Mar Apr ... Dec
Sales 1 5 6 8 11
Mkt 3 5 8 0 2
Adm 1 4 6 7 1

Thanks,

Glenn