How to ignore #N/A in the summation of a column

[jamesc1987]

Hello everybody.

I am having a problem with a complicated workbook I have inherited at work.

Currently, a cell with the formula =SUMIF(D$3:D$149,1,H$3:H$149) is returning a #N/A value because there are several #N/A cells in column H.

How would I amend the formula to ignore any #N/A cells?

I have tried the following =SUMIF(D$3:D$149,1,H$3:H$149,"<>#N/A") but have had no luck.

Any help is much appreciated!!

Regards.

 

[Jonmo1]

Welcome to the board...

You might be better served by eliminating the #N/A errors at the source in column H.

What is the formula you have in column H?
What version of Excel are you using?

 

[prajul89]

Why don't you consider converting that #N/A to "0" or something else using IFERROR????

 

[jamesc1987]

The cells that are returning #N/A are somehow sourcing data from an IBM sales database, if there is a sale the #N/A will change, so I want to avoid changing any values for the cells in column H if possible.

Here is a typical formula for one of the cells in column H:

=VLOOKUP($A137,'Rolling # Tests'!$A$2:$AT$76,ReportMonth,FALSE)


If possible I would like to leave all of the cells in column H as they are, and manipulate the formula I am using to try and add them together.

Thanks for the suggestions.

 

[Jonmo1]

Is there some formula in column H?
The formula can be adjusted to return 0 if it's #N/A
And still return the correct values otherwise.


If you insist on leaving column H alone,

Try this array formula entered with CTRL + SHIFT + ENTER
=SUM(IF(ISNUMBER(H$3:H$149),IF(D$3:D$149=1,H$3:H$149)))

 

[jamesc1987]

Hi Jonmo1,

I thought that new formula had promise but unfortunately it returned a value of 0. (Clearly wrong).

I'm afraid I have to leave the column H formulas as they are, the workbook was not built by me and is incredibly complex and interlinked, I don't have the expertise or confidence to make changes.

As for summing up the contents of column H, I have been reading around 'SUMIFS' where you can choose multiple criteria, this sounds like it could work but again I'm afraid nothing I have tried works... probably the way I'm writing the formula.

 

[Jonmo1]

Hi Jonmo1,

I thought that new formula had promise but unfortunately it returned a value of 0. (Clearly wrong).

This is likely because you didn't enter the formula correctly.

It is an array formula that requires special entry.
After entering the formula
Highlight the cell with the formula and press F2
Then press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {brackets}

 

[Jonmo1]

Have a little confidence...

If you're at all worried, save a copy of the file.

Then change this
=VLOOKUP($A137,'Rolling # Tests'!$A$2:$AT$76,ReportMonth,FALSE)
to
=IF(ISNA(VLOOKUP($A137,'Rolling # Tests'!$A$2:$AT$76,ReportMonth,FALSE)),0,VLOOKUP($A137,'Rolling # Tests'!$A$2:$AT$76,ReportMonth,FALSE))

This will make them return 0 instead of #N/A
And a plain old sumif will work just fine.

 

[jamesc1987]

Jonmo!

You hero!! It worked

Thankyou very much!!

 

[Jonmo1]

Glad to help, thanks for the feedback..

Just out of curiousity, which solution did you use?
the Array formula to sumif ignoring #N/A errors
or
Change the vlookup to return 0 instead of #N/A