First Nth Column Sums

[peter8848]

Hi All,


Just trying to get a formula in workbook A to get Month to date figures from the data below in workbook B, say I need to get the Apple figure up to May, so in workbook A i had May in the title cell A1 but in A2 i should use the formula to get the sum of $26. I tried sum(if but this can only add one column rather than multiple columns, also tried sum(offset but this does not work with the data in a different workbook data and lastly tried to concatenate sum formula from Range Column A to Nth Column dependence on the month but cannot get the sting into a formula even using indirect. Any better suggestion would be much appreciated. thanks for your help. Cheers, Peter

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Apple	7	1	2	10	6	6	10	6	2	1	9	10
3	Orange	2	9	4	2	8	8	10	3	2	2	4	8
4	Mango	6	2	6	5	3	0	5	2	7	5	9	6

<tbody>
</tbody>

 

[Glove_Man]

Can you edit Workbook B?

If so, an extra row with 1-12 instead of Jan, Feb etc would allow you to use SUMIF().

 

[Glove_Man]

Why wouldn't OFFSET work for you?

I got it to work with this:

=SUM(OFFSET('[Workbook B.xlsx]Sheet1'!$B$2,0,0,1,MATCH(A1,'[Workbook B.xlsx]Sheet1'!$B$1:$M$1,0)))

 

[Fluff]

Another option

Book1
	A	B
1		May
2	Apple	26
Paste

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(([Book1]Sheet1!$A$2:$A$4=A2)*(MONTH([Book1]Sheet1!$B$1:$M$1&1)<=MONTH(B1&1)),[Book1]Sheet1!$B$2:$M$4)

 

[peter8848]

Hi Glove_Man,

Thanks for your reply.

It just i some post saying it will not work with external workbook while i could not work it out. Also could yours work in a range like what Flutt had? Just want to learn if possible and what does that ,0,0,1 stand for in "Sheet1'!$B$2,0,0,1"?

Cheers,

Peter

 

[peter8848]

Hi Fluff,

You are a champion and it works!!!!

Sorry just wondering what is that &1 before the two brackets stand for? thanks for your help again.

Cheers,

Peter

 

[Fluff]

You're welcome & thanks for the feedback
It enables xl to convert a text month such as Aug into a number (ie 8)

 

[peter8848]

Hi Fluff,

Sorry one more question please, if is possible to amend your formula a bit to get the average of these value instead of the sum?

Thanks in advance.

Peter