Forum Discussion
Solved
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 th...
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.
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