# Sum of alternate columns with subtotals

#### HJimHB

##### New Member
Hi

I have a report which pulls values from 2 sources (13 from each) for each month of the year, so 312 columns in total.

For each line, I need to subtotal each source per month to compare these totals, and also get the annual totals for comparison.

How can I get this?

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### HighAndWilder

##### Board Regular
Are you able to provide a screenshot of the source worksheets?

Also an idea as to how you would like the report to be structured.

#### HJimHB

##### New Member

Hi - see (edited) image above. I need to add all the "Cog" and "SFA" columns to reconcile the totals for each month, and also to reconcile the annual totals, for each row (around 200 in total).

The column headings on row 2 are the same each month; I had to do in to add the months to row 1 in order to easily identify when one month's figures end and the next month's start.

In terms of what the final table would look like, I was trying to have 3 columns per month (Cog total, SFA total, difference) and then 3 for the year.

Thank you

#### HighAndWilder

##### Board Regular
The ease at which one can tackle a task such as this is often determined by how the raw data is structured.

Whilst not wanting to go into the concept of normalisation (see link below) I suggest that you restructure how you save your raw data.
1NF, 2NF, 3NF and BCNF in Database Normalization | Studytonight

Each column should represent 'something' and the single row column header should identify what the column represents. Each colum header must be unique.

Each row should represent 'something' and ideally the first column value should represent what the row represents. I'm not sure what your rows represent.
What is in column A?

Date
CogSFACode
RowIdentificationID

then the structure would be long and thin, something which you should nearly always aim for in raw data.
It's well worth putting the effort in. It can then be filtered and sorted easily

You could then use some of the standard Excel functions such as SUMIF, SUMIFS and COUNTIF, COUNTIFS and the

Aiming for a long and thin report table is also a good idea. Having to scroll horizontally is always awkward.

Can you post the raw data in a workbook and include some data that identifies what the rows represent and I'll have a look at it tomorrow whilst I wait for
Corona Virus to go away!!! It may be that I can write some code to restructure the data.

Take care and be safe.

#### HJimHB

##### New Member
Hi

Thank you for the response. Unfortunately the raw data is a report that we run from a third-party system and we have no control over the formatting. Is there an easy way to reformat the reports to be in the order you suggest?

We need to run the reports at least every month and often more frequently than that so ideally I was hoping to build something where I could paste the report into a sheet and the formulae would extract the necessary information. Is that possible?

I've gone through the file and redacted any confidential info; how can I upload this (sorry, new to the forum)?

Thank you, and hope you're safe as well.

#### HighAndWilder

##### Board Regular
I am safe and well Thanks. Plenty of food and facilities to self isolate.
I trust that all is ok with you.

Apparently one cannot upload a file but thats not the end of the world.
I just need some more information to enable me to think how to convert the data into a better structure.

1. Does each sheet of data only cover one year?
2. Are there only ever 12 columns per month? Never any less?
3. What is in column A?
4. What is in column B?

Thanks

#### Fluff

##### MrExcel MVP, Moderator
Maybe something like

+Fluff.xlsm
ABCDEFGHIJKLMNOPQRST
1JulyJulyJulyJulyJulyJulyAugAugAugAugAugAugJulyJulyAugAug
2Cog on ProgSFA on ProgCog BalSFA BalCog AimSFA AimCog on ProgSFA on ProgCog BalSFA BalCog AimSFA AimCogSFACogSFA
3123456789101112912-32730-3
4121110987654321302731293
Data
Cell Formulas
RangeFormula
O3:P4,R3:S4O3=SUMIFS(\$B3:\$M3,\$B\$2:\$M\$2,O\$2&"*",\$B\$1:\$M\$1,O\$1)
Q3:Q4,T3:T4Q3=O3-P3

#### HighAndWilder

##### Board Regular
Thanks

So what does the value in cell B3, for example represent?

An instance of 'something' that happened in July for which the code 'Cog on Prog' applies?

The idea is to have the raw data once it has been restructured in one sheet and the analysis in another.

#### Fluff

##### MrExcel MVP, Moderator
I'm not the OP
I was just suggesting a possible solution, with some simplified data

#### HJimHB

##### New Member
Hi both

This is financial information that pulls from two sources (Cog and SFA) that we need to reconcile. For each month, there are 26 columns, 13 from each source, that we need to subtotal for each month to reconcile, and then get a total for the year.

Thanks