Sum of alternate columns with subtotals

[HJimHB]

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.

 

[HighAndWilder]

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]

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]

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]

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]

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]

Maybe something like

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		July	July	July	July	July	July	Aug	Aug	Aug	Aug	Aug	Aug		July	July		Aug	Aug
2		Cog on Prog	SFA on Prog	Cog Bal	SFA Bal	Cog Aim	SFA Aim	Cog on Prog	SFA on Prog	Cog Bal	SFA Bal	Cog Aim	SFA Aim		Cog	SFA		Cog	SFA
3		1	2	3	4	5	6	7	8	9	10	11	12		9	12	-3	27	30	-3
4		12	11	10	9	8	7	6	5	4	3	2	1		30	27	3	12	9	3
Data

Cell Formulas
Range	Formula
O3:P4,R3:S4	O3	=SUMIFS($B3:$M3,$B$2:$M$2,O$2&"*",$B$1:$M$1,O$1)
Q3:Q4,T3:T4	Q3	=O3-P3

 

[HighAndWilder]

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]

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

 

[HJimHB]

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