Excel: 'Sumifs' ignoring #n/a

user_13982510 0
Viewed 1972

I'm trying to sum some values (both positive and negative) in a column, but it won't sum as there are #n/a values. Is there any way to get around this? Please see the example below:

enter image description here

Each city has lower areas that have income,crime & unemployment scores (either positive or negative). My goal is to get city-wide income, crime and unemployment scores by summing lower areas' scores. I'm using 'sumifs' in G2 as in =SUMIFS(C$2:C$10,$A$2:$A$10,$A2) and then dragging it to I10. In this toy example, there are only 10 rows, but my data have 1 million rows, so I cannot really do dragging. Any suggestions on that would also be helpful!

But, most importantly my problem is that I cannot use 'sumifs' due to the #N/A values. I want to ignore them.

Or simply replacing all the #N/A values with 0 in the 1 million rows would also be an option.

p.s. I have done some research on previous similar questions, but they seem to use different 'sumifs' formulae...

excel-formula excel
2 Answers

Using SUMIFS with <0 AND >0

You could use this:

=SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,"<0")+SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,">0")

It avoids zero (which makes no difference in this case). It also avoids cells that contain values that are not numbers, such as NA, which often prevents summing (avoiding these solves your issue in this case).

If I understand it correctly, you want to get the sum of both positive and negative numbers (i.e. their existing values, not absolute values), however that is currently being prevented using your existing approach by the presence of the NA items.

If this is not correct, please advise.

USING AN INTERIM LOOKUP, THEN SUMIF WITH <0 AND >0

You could alternatively insert a lookup column, which concatenates the values to evaluate, and then use SUMIF.

For example, new column J:

=$A2&C2

(noting the $ for the A as well as the absence of $ for the C)

Then fill J1 to the right to K1 and L1 Then in M1:

=SUMIF(J$2:J$10,"<0",C$2:C$10)+SUMIF(J$2:J$10,">0",C$2:C$10)

Then fill right to N1 and O1

The potential issue with this approach is if you fill down millions of rows the spreadsheet may slow down significantly.

REPLACING NA

Replacing the NA with 0 will also work, but you may not wish to lose the distinction between "0" and "not shown in source data".

Please post your existing formula for the NA values if you would like to go down that path. They may be reworkable into something instead of NA that is meaningful and does not prevent summing.

It is often preferable when looking up data to trap for the possibility of NA and return some other more meaningful (or in this case, more sum-friendly) result.

user_141529890

Perhaps the easiest way is to use the "<>#N/A" criteria as in =SUMIFS(C$2:C$10,$A$2:$A$10,$A2, C$2:C$10, "<>#N/A") which will ignore NA in the sum column. You could add additional criteria for other conditions columns if they are interfering with numeric conditions checks.

Thanks: https://www.mrexcel.com/board/threads/sumifs-while-ignoring-n-a.1042338/ for the tip.

user_12241580
Related