SOLVED

# SUMIFs

Occasional Contributor

# SUMIFs

Hi. If i have the following formula for instance SUMIFS(\$D\$3:\$D\$2000,\$A\$3:\$A\$2000,"*"&F3&"*"), and It brings #N/A for some result, how can i remove the #N/A, as the "<>#N/A" add on, which i put at the end, doesn't seem to work, unless i've added it to the wrong area in the formula. Any advise? Thank you

9 Replies

# Re: SUMIFs

Which of the ranges contains #N/A? D3:D2000 or A3:A2000 or both?

# Re: SUMIFs

Depends on Excel version, on 365 or 2011 that could be

``=SUM( IFNA( \$D\$3:\$D\$2000, 0) * ISNUMBER(SEARCH( F3, \$A\$3:\$A\$2000 ) ) )``

# Re: SUMIFs

@Hans Vogelaar Hi Hans, the #N/A comes from D range. Although I would probably need it for both?

# Re: SUMIFs

@sergeibaklan Hi Sergei, we're on version 365, thanks

# Re: SUMIFs

Sergei's suggestion should do what you want.

# Re: SUMIFs

@Hans Vogelaar Hi Hans, so i did try this formula, and it just gave me #value error, even on the cells were there use to be a correct value on. Sorry!

# Re: SUMIFs

Does it help if you confirm the formula with Ctrl+Shift+Enter?

I have attached a small sample workbook that shows Sergei's formula in action.

best response confirmed by sinadanaee (Occasional Contributor)
Solution

# Re: SUMIFs

@sinadanaee  ...  The following works fine for me.  See the attached Excel file.

A1:

=SUMIFS(D3:D13, D3:D13, "<>#N/A", A3:A13, "*"&F3&"*")

NOTE:  No need for the condition A3:A13,"<>#N/A".  It is redundant.

PS.... I tested with the F3 condition first, and it appears that order of the conditions does not matter.

Aside.... I would prefer to avoid the #N/A errors in the data, in the first place.

# Re: SUMIFs

@Joe User Yes, perfect, that seems to have done the trick. Many thanks to all for your help