Forum Discussion
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...
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,"")