Same Formulas for Multiple Repeating Columns

[aiste_griffiths]

I have a raw data set that has 277 columns (5 columns per Month).

If you look at the raw excel data, it's actually split in the middle (row 44: Removed Devices). Rows above that are Active Devices and rows below that are Removed Devices.

For each month in this dataset (Jan 2020 to July 2024)  I need to calculate:

1. Total cost ex gst

2. Total Cost: Rental

3. Total Cost: Transaction

Total cost ex gst is a sum of column "total cost ex gst" (both active and removed devices)

Total Cost: Rental is a sum of column "rental" (both active and removed devices)

Total Cost :Transaction is a sum of columns Withdrawals + Deposits + Balance Enq (both active and removed devices)

I have been trying to apply Transpose, Cross Tab and Multi Row formula tools for hours now with no luck 😫!! Could you please guide me how to manipulate this particular data to get desired sums for each month and to display it on the same sheet? Thank you!

[Qiu]

@aiste_griffiths 
Thank you for your follow-up. Take your time.
I really like this one, maybe we can submit it as weekly challenge idea after your confirmation.

[CoG · Accepted solution]

Here is another alternative that should be just a tad simpler:

Hope this helps and Happy Solving!

[Qiu]

@CoG 
Happy to see that we seems to have the same numbers😁

[aiste_griffiths]

Thank you so much @flying008 @Qiu @CoG 

what an amazing community! ❤️

[Qiu]

@aiste_griffiths 
Glad we helped.