Search code examples
excelcountifnamed-ranges

Excel COUNTIFS Issue

=COUNT(Due_Date<IF(ISBLANK(Final_Report_Date),Due_Date-1,Final_Report_Date)) gave me the result 1 which is correct.

But when i use =COUNTIF(Due_Date,"<"&IF(ISBLANK(Final_Report_Date),Due_Date-1,Final_Report_Date)) it gave me 131. Why those two does not return the same result?

Both Due_Date, and Final_Report_Date are named range.

Solution

  • Such comparisons are best handled by SUMPRODUCT:

    =SUMPRODUCT(0+(Due_Date<N(+Final_Report_Date)))

    Regards