Excel: Countifs and Sumprod not producing same result when they should


Here are two formulas that should produce the same result: =data1!C$2)+('all emails date'!$D$3:$D$164000="-"))) ="&data1!C$2)+ COUNTIFS('all emails date'!$B$3:$B$164000,"<"&data1!C$2,'all emails date'!$D$3:$D$164000,"="&"-") The formula using countifs is correct. It produces ~8000. The formula using sumproduct is incorrect and produces ~14,000 Cell data1 C2 is the first of January 2014 1/1/14. All emails date! column D is a date field or, if not applicable, a dash "-". All emails date! column C is a date field and has only dates. Why are the results so different? I know, trust and love sumproduct and do not want to give it up.


This question generated 15 answers. To proceed to the answers, click here.

This thread is current as of September 23, 2014.


For more resources for Microsoft Excel