sumifs based on column header

dirtywizard

New Member
Joined
May 1, 2015
Messages
32
Hi

I have a workbook with three sheets

1) staging sheet full of floating column headers (changed as required by colleagues)
2) a sheet populated by a macro that aggregates the results from the staging sheet into a weekly breakdown
3) a summary sheet


In the summary sheet I have a table that uses VBA to get the column headers from sheet 2 and then is attempting to summarise the weekly figures. I've created a total summary successfully by using

=sum(index(weeklyoutput,,match(b8,weeklyheader,0))),0)

which matches the column headers in the summary sheet with the weekly sheet and pulls back the sum
(weeklyoutput is a dynamic range name covering the full table in sheet 2 and weeklyheaders are the headings in sheet 2)

I want to make another summary table that does the same thing but with a twist - static column C in sheet 2 is a channel view (e.g email, online displays etc)

sheet 3 (the summary tab) contains a channel list and I want to do a variant of the above formula to summarise by channel.

e.g. if on the summary tab column B (row 23) is "email", and there is a spend heading in column C, I want to get the summary of the spend for the email channel.

Because the column headers and orders can be changed on the fly in sheet 1 (and thus sheet 2) I can't do a standard sumif - I need to use something like the above index/match but bring in the condition of checking against column B.

Any ideas, as I'm at a loss...

Thanks in advance
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top