Forum Discussion

Memoree Van Meer's avatar
Memoree Van Meer
Copper Contributor
Apr 19, 2018

nested IF formula returning #N/A error

I am attempting to have a formula do the following: If column L is greater than or equal to AL21 and if column L is less than AM21 and if column M is equal to "Y" then sum all of the blanks in column O (AL21 and AM21 are dates).  To do this, I came up with the following formula: =SUM(IF('CAPA Tracking'!$L$4:$L$2878>=AL21,IF('CAPA Tracking'!$L$4:$L$2878<AM21,IF('CAPA Tracking'!M:M="Y",IF(ISBLANK('CAPA Tracking'!$O$4:$O$2878),1,0),0))))  When I ctrl+shift+enter for the array formula, I get a #N/A error.  I have a similar formula in another part of the spreadsheet that works.  The part I added to this one is "IF('CAPA Tracking'!M:M="Y"," so I know that's the part it's struggling with.  What I don't know is if I need something different because I'm asking it to look for a text value (the "Y") or if I need something different here entirely, an AND or a SUMIF or something.  Thanks for any help!

Formulas & Functions
Need Help
  • Logaraj Sekar's avatar
    Logaraj Sekar
    Steel Contributor
    Apr 20, 2018

    Hi,

     

    Problem is you only specified conditions only (on which basis you want to sum), you forget to mention which value you have to sum. And IF('CAPA Tracking'!M:M="Y" will not work here.

     

    Clarify the following. I will solve your question. 

     

    Note: In this case, use SUMIFS formula.

    =SUMIFS('specify the range you want to sum',L:L,">="&AL21,L:L,"<"&AM21,M:M,"Y",O:O,"")

     

    1. Which column you want to sum.

    2. In which columns you have date values.

     

     

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 20, 2018

    Hi Memoree,

     

    You have #N/A since in your conditions are regions of different size. If you change in your formula M:M on $M$4:$M$2878 it shall work

    =SUM(IF('CAPA Tracking'!$L$4:$L$2878>=AL21,IF('CAPA Tracking'!$L$4:$L$2878<AM21,IF('CAPA Tracking'!$M$4:$M$2878="Y",IF(ISBLANK('CAPA Tracking'!$O$4:$O$2878),1,0),0))))

    Above is array formula, same result gives regular (not array) formula with SUMPRODUCT

    =SUMPRODUCT(('CAPA Tracking'!$L$4:$L$2878>=AL21)*('CAPA Tracking'!$L$4:$L$2878<AM21)*('CAPA Tracking'!$M$4:$M$2878="Y")*ISBLANK('CAPA Tracking'!$O$4:$O$2878))

    However, form performance point of view even better to use COUNTIFS to count number of blank cells

    =COUNTIFS('CAPA Tracking'!$L$4:$L$2878, ">="&AL21,'CAPA Tracking'!$L$4:$L$2878, "<A"&M21,'CAPA Tracking'!$M$4:$M$2878,"Y",'CAPA Tracking'!$O$4:$O$2878,"")

     

Resources