# Add all absolute values of items in excel without using abs() for each range

1. ## Add all absolute values of items in excel without using abs() for each range

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:

XXXX | XXXXX | XXXX
XXXX | XXXXX | (XXX)
XXXX | XXXXX | Subtotal

XXXX | XXXXX | XXXX
XXXX | XXXXX | (XXX)
XXXX | XXXXX | Subtotal

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

2. ## Re: Add all absolute values of items in excel without using abs() for each range

It sounds like all of the data is in one column. How about something like this:

=SUM(IFERROR(ABS(C2:C14),0)) Ctrl Shift Enter

3. ## Re: Add all absolute values of items in excel without using abs() for each range

Thanks 63falcondude,

However, my table actually has subtotals which I must exclude from my sum. I've updated my original post to show this

4. ## Re: Add all absolute values of items in excel without using abs() for each range

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
6) Click on "Close this window"

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1