Excel COUNTIFS Issue

Question

=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.

Does Due_Date refer to a range of more than one cell? Or to a single cell? — XOR LX, May 14 '18 at 09:39
A Named Range can refer to a single cell as well as to a range of more than one cell, so that doesn't answer my question. — XOR LX, May 14 '18 at 09:43
Your first formula is logically flawed, and will always return 1, no matter what values the ranges contain. Suggest you test this yourself. — XOR LX, May 14 '18 at 09:47
Does your construction of the criteria in the second actually work? — Solar Mike, May 14 '18 at 09:55
what i am trying to do trying to check due date < final report date, but there are some blanks for final report date, then i will exclude those. The result should be 1 but it is giving me 131. — kevin, May 14 '18 at 10:07
@XORLX, yeah you are correct. It is a coincidence that it gave me correct result. — kevin, May 14 '18 at 10:14

score 1 · Accepted Answer · answered May 14 '18 at 12:14

1

Such comparisons are best handled by SUMPRODUCT:

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

Regards

answered May 14 '18 at 12:14

XOR LX

7,632
1
16
15

It works perfectly. But don't understand how it works. Sorry, i just learn excel like 4 months ago. Sumproduct is to multiple one array with another array to my understand. Could you explain me how your formula works? – kevin May 16 '18 at 11:22

Excel COUNTIFS Issue

1 Answers1