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.

 

[jamesc1987]

I used:

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

Mainly because the data is much more comprehensible with 0's instead of #N/A's.

It turns out you're other method worked too, I just hadn't been holding Cntrl, Shift and Enter.

Thanks again.

 

[Jonmo1]

Good, that is the better solution in the long run...