Excel Formula

Occasional Contributor

I have attached a spreadsheet that I need help with.  The first tab is a summary total page and then I have tabs for each month.  I would like to create a formula that would total all tabs (January to December) if Column K equals either "T" or "R" and if the customer number in Column B and the Service # and Bin Size are the same.

I know that is is possible somehow, however I am not sure how to get it working properly.

 

Thanks in advance.

1 Reply

@Larry1921 Wouldn't recommend to try doing this with formulae. Even when possible, the formulae will probably be monstrous.

The easy way out would be to copy all data from the month tabs into one sheet, under one header row. Then, custom sort (first by customer number, then by service number, then by bin size) and add sub-totals. Alternatively, you could use PowerQuery to do something similar, though more robust. The output could then look something like in the picture below, showing only a header row and the bottom part of the summary table.

Screenshot 2021-12-26 at 08.37.44.png

By the way! I noticed that as from April the table lay-out changed. Columns O and P were added. If you want to make any (semi-)automated solution work, you need to insert two blank columns in each sheet for the first quarter.