How to sum 'x' number of columns based on another criteria

[bat18]

Hi

I am probably looking for 2 pieces of help.

I am looking for a formula that will sum the last 12 columns in a table that has some sort of data in that column.
For example the range may be 16 columns by 1000 rows. Only maybe 500 of those rows will have data in the final column but i still want to sum the last 12 columns of all 1000 rows.

I think this may be split in 2 chunks. 1st bit i could do a count to see how many columns have data in which will come back as 16. Now how do i sum the last 12 columns ending in column 16?

The last bit is the most important for me.

All help appreciated.

 

[Jonmo1]

Will there be gaps in each row?
Say A1 has number, B1 is empty, then C1 has a number again?

Or will the numbers all be consecutive untill there is an empty?

 

[bat18]

There could be gaps, they could be continuous.

I would say i have found a way to work out the total number of columns with some form of data in.

What i need is a forumla that will take this number and sum 11 previous columns (and itself) to give me the last 12 months worth of data. And i mean a normal sum as well not summing everything that isn't blank.

So if my number of columns with data is 26. I want a formula that says sum(O1:Z1).

If that number goes up to 27 i want the formula to update automatically to sum(P:AA)

Cheers

 

[Jonmo1]

I would say i have found a way to work out the total number of columns with some form of data in.

What i need is a forumla that will take this number and sum 11 previous columns (and itself) to give me the last 12 months worth of data. And i mean a normal sum as well not summing everything that isn't blank.

So if my number of columns with data is 26. I want a formula that says sum(O1:Z1).

If that number goes up to 27 i want the formula to update automatically to sum(P:AA)

Cheers

So you already have a formula that tells you the 26 or 27 right?
In my suggested resolution, I'm going to put an X to represent that number.

=SUM(OFFSET(A1,0,X-11,1,12))

 

[bat18]

For some reason i need to start at one column to the left of A1 but other than that, perfect.

Cheers

 

[Jonmo1]

My Bad, change to
=SUM(OFFSET(A1,0,X-12,1,12))

 

[Jonmo1]

Oh, and for error handling if there are less than 12 numbers total in the range..

=SUM(OFFSET(A1,0,MAX(0,X-12),1,12))

 

[bat18]

Perfect, cheers for your help.

 

[bat18]

Hi again

I am now looking to move this formula on.

I want to know how i can incorporate a VLookup into this (or possibly index&match.

Basically i have a list of data (call it Data x) in sheet 1. In sheet 2 I have 'Data x' along with corresponding values in many columns to the right hand side.

In sheet 1 i would like to sum the last 12 columns (as worked out before) but using some sort of vlookup to sum the data from sheet 2 that matches that in sheet 1.

If this is not clear let me know. Cheers