Forum Discussion

sinadanaee's avatar
sinadanaee
Copper Contributor
Jan 10, 2022
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...
excel
  • JoeUser2004's avatar
    JoeUser2004
    Jan 11, 2022

    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's avatar
JoeUser2004
Bronze Contributor
Jan 11, 2022

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.

 

sinadanaee's avatar
sinadanaee
Copper Contributor
Jan 12, 2022

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