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.

     

HansVogelaar's avatar
HansVogelaar
MVP
Jan 10, 2022

sinadanaee 

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

sinadanaee's avatar
sinadanaee
Copper Contributor
Jan 11, 2022

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

    • sinadanaee's avatar
      sinadanaee
      Copper Contributor
      Jan 11, 2022

      HansVogelaar 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!

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 11, 2022

        sinadanaee 

        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.