I have to do a sum of the absolute value of all of the items in many different tables in excel. Is there a way I can do this without typing abs(range) for each range that I want to sum.
What my data looks like:
Header | Header | Total
XXXX | XXXXX | XXXX
XXXX | XXXXX | (XXX)
XXXX | XXXXX | Subtotal
Header | Header | Total
XXXX | XXXXX | XXXX
XXXX | XXXXX | (XXX)
XXXX | XXXXX | Subtotal
Header | Header | Total
XXXX | XXXXX | XXXX
XXXX | XXXXX | (XXX)
XXXX | XXXXX | Subtotal
I would need to take the sum of the absolute values of all of the totals. I've tried doing and array formula such as {sum(abs(C2:C4;C7:C9;C12:C14))}, but this doesn't actually work. Now I could do a sum where I use an array formula, and do ABS(range) with every table individually such as {sum(abs(C2:C3);abs(C7:C8);abs(C12:C13)))}, but there are much more than 3 tables, and this would be quite time consuming. Is there a better way of approaching this?
Thanks
(edit) - changed the tables to show that there are subtotals which must be excluded in my table
Bookmarks