Conditional Sum On #N/A Text Values

Today’s post is inspired by a question which came up over at the MrExcel.com forums.

Here’s my spreadsheet:

NASpreadSheet

It’s pretty simple. In cells A1:A2 I have the formula ="#N/A" and in cells A3:A4 I have the formula =NA(). Whilst the outputs of these formulas look similar, they are fundamentally different: A1 and A2 are text types whilst A3 and A4 are error types. The respective left and middle alignments in the cells hint at this.

The challenge is how to use SUMIF() to get a sum of the values in column B which correspond to the text "#N/A" values – and it isn’t quite as easy as you’d think. We want an answer of 3 but all of these formulas return 12:

=SUMIF(A1:A4,"#N/A",B1:B4)

=SUMIF(A1:A4,"=#N/A",B1:B4)

=SUMIF(A1:A4,A1,B1:B4)

It appears that, under the hood, SUMIF() coerces each of these criteria arguments from text types into error types before it does its comparisons. Before you read on have a go at finding the solution.

After a bit of playing around, here’s the best I could come up with in terms of pure SUMIF() formulae:

=SUMIF(A1:A4,"*#N/A",B1:B4)

Or variations of:

=SUMIF(A1:A4,"?N/A",B1:B4)

=SUMIF(A1:A4,"#?/A",B1:B4)

etc…

The * and ? wildcards convince SUMIF() that we want it to work with text types. If you’re not familiar with using wildcards in SUMIF() then check out this blog post I wrote a couple of years ago.

Of course, these formulae aren’t foolproof. For example, if A1 contained the formula ="abc#N/A" then it would be included in the * wildcard sum. We only want pure "#N/A" values to be included. To cater for those somewhat unlikely scenarios I ended up tweaking the formula to a SUMIFS():

=SUMIFS(B1:B4,A1:A4,"?N/A",A1:A4,"#???")

I think that one’s solid. Another option, which I’m not nearly as keen on, is this array formula (CTRL + SHIFT + ENTER):

=SUM((IFNA(A1:A4,"")="#N/A")*B1:B4)

So, can you improve on the SUMIFS() formula or, even better, find a robust way of doing it with SUMIF()?

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , . Bookmark the permalink.

One Response to Conditional Sum On #N/A Text Values

  1. Interesting article… I can definitely see many different applications for the wildcards, especially. Thanks for sharing!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s