Summing with Merged values

Brass Contributor

Hello!  I am trying to do a mass sum of different filters, but not sure how to go about it.

 

For example with the data attached, I need to sum all of the TMMC North information by month (April, May, June....) and then also TMMC West, TMMC South, ect for each month.  The Assembly, Weld, and Paint information all need to be summed into one.  

 

So If the cell is TMMC North, then sum all of the April data.

 

Sheet 2 has the information that needs to be summed, and Sheet 3 is the format I need the data in.

 

I tried using a sum product - but got super lost in the process.  Wondering if there was an easier way!

 

Thanks in advance!

1 Reply

@nattiej101 

You don't identify which version of Excel you are using, on which platform. I will assume a Windows desktop version.

 

First, by all means make your Shift NPD worksheet properly viewable: select cell D3, then in the Window section of the View menu click Freeze Panes and then the Freeze Panes option beneath that. Then when you scroll to the right, the first three columns remain visible (and similarly for the top two rows when scrolling down).

 

Within the workbook, I do not know what the values North, West, South, L1, L2, and L3 represent (specific facilities? regions?); I will refer to them as places. The fact that the place names are unique across the TMMC and TMMK values (whatever those represent) helps keep the formulas simple.

I wrote several types of formulas for creating the sums. In the Fo1,Fo2 grid, the formulas rely on the constituent rows being spaced 6 rows apart. Formula1 uses end-of-range column identifiers (like DU); OTOH, Formula2 uses the number of days in each month to calculate how many columns of the source worksheet to include in the ranges. Neither formula is particularly easy to maintain - to copy to a new column, you have to edit the identifier for the first-shift-of-month column, and either the identifier for the last-shift-of-month column or the number of days in that month.

 

In the Fo3,Fo4 grid, I gave up on creating Formula3 before I started - it would be a monster formula. But if your version of Excel supports the LET function, Formula4 is usable (but giant). It determines the appropriate starting row in the source worksheet for a place name by using the MATCH function with the place name in this grid. Like the formulas in the prior grid, it relies on the other rows for the place being six rows apart.

 

The use of Formula4 for other years is possible, but the 2022 literal inside the first DATE function would have to be changed appropriately. (The DATE function for 2022-03-30 is used in deriving column numbers, as your data starts in April.) The formula calculates the number of days in the month (but does not handle leap years!) based on the Excel column number, counting from the left; that's not ideal. Those calculations would be simpler or unnecessary if the grid contained date values rather than "April", "May", etc., literals. So…

 

In the Fo5 grid, I replaced those text literals with date-time values and formatted them to display just the month names. (I could have used any date-time within the month, but I chose to use the last second of the last day of each month for its utility in later calculations.) And, I secretly replaced the Shift numbers on the Shift NPD sheet with date-time values, but formatted them to display 1 or 2. That's useful, because with appropriate date-time values there, you can readily use FILTER functions, as I did on the intermediate worksheet "(Month Rollup)", to SUM all the shifts that fall within a month (and leap years are handled correctly, too). Then Formula5 only needs to aggregate the monthly data for the place names in its row (and the place name in that row can appear in any number of rows in the rollup sheet, with any spacing).