Here are two formulas that should produce the same result:
<data1!c$2)*
SUMPRODUCT(
('all emails date'!$B$3:$B$164000) *<data1!c$2)*
(('all emails date'!$D$3:$D$164000>=data1!C$2)+('all emails date'!$D$3:$D$164000="-")))
<data1!c$2)*
AND
COUNTIFS('all emails date'!$B$3:$B$164000,"<"&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.</data1!c$2)*
</data1!c$2)*
</data1!c$2)*
<data1!c$2)*
SUMPRODUCT(
('all emails date'!$B$3:$B$164000) *<data1!c$2)*
(('all emails date'!$D$3:$D$164000>=data1!C$2)+('all emails date'!$D$3:$D$164000="-")))
<data1!c$2)*
AND
COUNTIFS('all emails date'!$B$3:$B$164000,"<"&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.</data1!c$2)*
</data1!c$2)*
</data1!c$2)*
Last edited: