# Thread: First Nth Column Sums Thanks: 0 Likes: 0

1. ## First Nth Column Sums

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

2. ## Re: First Nth Column Sums

Can you edit Workbook B?

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

3. ## Re: First Nth Column Sums

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)))

4. ## Re: First Nth Column Sums

Another option
AB
1May
2Apple26

Paste

Worksheet Formulas
CellFormula
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)

5. ## Re: First Nth Column Sums

Hi Glove_Man,

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

6. ## Re: First Nth Column Sums

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

7. ## Re: First Nth Column Sums

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

8. ## Re: First Nth Column Sums

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?

Peter