Sum of alternate columns with subtotals

HJimHB

New Member
Joined
Dec 2, 2019
Messages
9
Office Version
365
Platform
Windows
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?

Thanks in advance.
 

Some videos you may like

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
Joined
Nov 4, 2006
Messages
173
Office Version
365
Platform
Windows
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
Joined
Dec 2, 2019
Messages
9
Office Version
365
Platform
Windows
1584987819809.png


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
Joined
Nov 4, 2006
Messages
173
Office Version
365
Platform
Windows
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?

If you had a structure with the following column headers :

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
reporting task will be made a lot easier.

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
Joined
Dec 2, 2019
Messages
9
Office Version
365
Platform
Windows
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
Joined
Nov 4, 2006
Messages
173
Office Version
365
Platform
Windows
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
Joined
Jun 12, 2014
Messages
44,505
Office Version
365
Platform
Windows
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
Joined
Nov 4, 2006
Messages
173
Office Version
365
Platform
Windows
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
Joined
Jun 12, 2014
Messages
44,505
Office Version
365
Platform
Windows
I'm not the OP ;)
I was just suggesting a possible solution, with some simplified data
 

HJimHB

New Member
Joined
Dec 2, 2019
Messages
9
Office Version
365
Platform
Windows
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,932
Messages
5,508,193
Members
408,670
Latest member
lhmwnrexcel

This Week's Hot Topics

Top