Sum absolute value based on multiple specific criteria

[TacticalExcel]

Hi guys,

Current Format and Background:

I am trying to sum the absolute value of a long list of positive and negative values that also contains #N/As. Currently I have written an array formula that looks like this:
=(SUM(IF(ISNUMBER(Plants!$P$1676:$P$1744),IF(Plants!$P$1676:$P$1744>0,Plants!$P$1676:$P$1744)))-SUM(IF(ISNUMBER(Plants!$P$1676:$P$1744),IF(Plants!$P$1676:$P$1744<0,Plants!$P$1676:$P$1744))))

The ISNUMBER function works to deal with the N/A values and I am returning the absolute value for the range shown.

My full range is much longer and contains data from multiple "Plants". I want to return the absolute value for each individual plant, and and so I have organized the data such that all data from the same plant is grouped together. In the formula I simply change the range to reflect the range of data for Plant X vs. Plant Y vs. Plant Z and so on.

The Issue

As I refresh my data however, the range of data for a specific plant may change, and so I want to write a formula that refers to the name of "Plant X" as opposed to the range of Plant X so that way I do not have to go back and check the range in my table for each plant and then re-enter the new range into each formula.

Unfortunately I do not know how to structure the formula in a way that does all 3 things I need it to do

1) Sum the absolute value
2) Ignore #N/As to prevent errors
3) Do the following two only for a specific named Plant.

Thank you in advance for the help. I am using Excel 2016.

 

[xenou]

I think the simplest would be to change the #N/A results to 0 in your values, so you could just use a simple SumIf() formula. For that matter, you can just add a "helper" column, with the value (if not N/A) and otherwise zero. Then use SumIf on your helper column. Some people don't like that kind of solution but I find it generally very convenient since the spreadsheet is thereby extremely easy to understand and maintain (even for people who know very little advanced Excel).

That said, you can just add the plant criteria to the formula:

{=SUM(IF($A$1:$A$9="Plant X",IF(ISNUMBER($B$1:$B$9),ABS($B$1:$B$9),0),0))}

 

[Scott Huish]

First off, that formula can be much shorter because Excel has an absolute value function ABS:

=SUM(IF(ISNUMBER(Plants!P1676:P1744),ABS(Plants!P1676:P1744)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

If you have named ranges, you can certainly substitute that into the formula:

=SUM(IF(ISNUMBER(PlantX),ABS(PlantX)))
Confirm with CTRL-SHIFT-ENTER