Forum Discussion
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
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.
9 Replies
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.
JoeUser2004 Yes, perfect, that seems to have done the trick. Many thanks to all for your help
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 ) ) )- @sergeibaklan Hi Sergei, we're on version 365, thanks
Which of the ranges contains #N/A? D3:D2000 or A3:A2000 or both?
HansVogelaar Hi Hans, the #N/A comes from D range. Although I would probably need it for both?
Sergei's suggestion should do what you want.
