Multi YTD Summary

[zy1972]

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

 

[Max]

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

 

[muddan madhu]

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)))&","&"mkt"&"
"&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)))

 

[zy1972]

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