sum from multiple columns based on criteria in multiple columns and embedded in header

r_simpson42

New Member
Joined
Sep 25, 2013
Messages
8
Please see example - I will try to explain, but the visual aid is probably required to understand what I am trying to do:
I have many columns in a spreadsheet and every fourth column has state abbreviations with data in the next and second to next columns. I want to sum up all this data in all the columns based on the state abbreviations in every fourth column and based on the presence of particular information embedded in the header of the columns.

Any suggestions are appreciated!

CO,KYCO,KYCO,INCO,IN
JurisdictionREFJurisdictionREF
Account: 1000 Reclasses 1000 1040 Reclasses 1040
icSalesMA (10,000,000)17 100,000,000 RI 5,164,568
icSalesCA 8,000,000 MA 9,500 4,131,654
icSalesIN 80,000,000 CO 9,000 41,316,544
icSalesMN 900,000,000 CA 50,000,000 464,811,119
icSalesAK 75,000,000 CT 38,734,260
icSalesIA 1,000,000 MS 516,457
icSalesOH 78,000,000 LA 59,765,412 40,283,630
icSalesPA 68,000,000 TX 35,119,062
icSalesCT 50,000,000 MA 6,000,000 25,822,840
sum MA for CO = 125,963,994 Add up all MA as CO is in all headers
sum CA for IN = 514,811,119 Add up only CA where IN is in header

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col></colgroup>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Based on your sample data, I would probably sum each column on it's own (and put the sub-totals off to the side), then sum each sub-total
 
Upvote 0
I was hoping for some ideas that would require less setup and less maintenance - mostly the less maintenance part. Any thoughts...? I wrote formulas all day yesterday and am fresh out of ideas.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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