First Nth Column Sums

peter8848

Board Regular
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>

Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Glove_Man

Well-known Member
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

Well-known Member
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)))

Another option

peter8848

Board Regular
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

peter8848

Board Regular
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

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

peter8848

Board Regular
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

1,102,633
Messages
5,488,022
Members
407,617
Latest member

This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...