Getting the sum of two intersecting named ranges

**mam04x** · 07-10-2012, 02:14 PM

I have a large table with several years worth of data, organized by date, including year/quarter/month/date. There are then several columns.

I've made the quarters into named ranges; Q1_2012, Q2_2012, Q3_2012, Q4_2012.

I then made the columns into named ranges; Account_A, Account_B, Account_C, etc.

I would like to find the sum of all cells in the column for Account_A where it intersects with the many rows that make up the named range for a specific quarter. I think the way to do it is with some sort of index formula, but I'm not totally sure.

I've attached a file for reference.

Thanks,
Mark

**Pepe Le Mokko** · 07-10-2012, 02:41 PM

You could use a Pivot Table and group by quarters, it even dispenses with creating the quarter ranges, or use something like

Please Login or Register  to view this content.

**K m** · 07-10-2012, 03:09 PM

Another approach may to use a "Filtered List"
Example: To get the sum of Account A for the first quarter
Filter the list for Q1 and put the following formula in cell F368
=SUBTOTAL(109,F2:F367). This would give you a total for the filtered list

You could filter for the other quarters by changing the filter to Q2, Q3 or Q4