Countifs and Sumprod not producing same result when they should

doug firr

Board Regular
Joined
Mar 28, 2011
Messages
140
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)*
 
Last edited:
Thanks to both of you for the info my spreadsheet is now working and I can move on!
Questions:
1) @barry I don't understand your explanation for using > 0? Could you elaborate?
2) @ gardnertoo what is double dash comma in sumproduct? I've not seen that before (Well I might have I recognize it but have never used it).
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
@barry I don't understand your explanation for using > 0? Could you elaborate?

Sorry, I didn't explain properly

In your original formula you are simply adding the two conditions, so when you have a "-" the result is 2 because both conditions are true, the 2 is then used by SUMPRODUCT so it double-counts.....but when you use

(('all emails date'!$D$3:$D$164000 >=data1!C$2)+('all emails date'!$D$3:$D$164000="-") >0)

any row where any of the conditions is satisfied returns a 1 or a 2 (in the case of "-") but when you use >0 both 1 and 2 return TRUE and when the TRUEs are multiplied with the other array they return 1s, so no double-counting.

In summary you can add conditions (an "OR" type setup) when they are mutually exclusive....but when one row might satisfy more than one condition (like here) you need to use the type of setup I proposed

 
Upvote 0
The double dash comma structure within the SUMPRODUCT function converts a list of text results into a list of numerical results, which can then be multiplied together (the PRODUCT portion) before then being added up (the SUM portion). Each test with the parenthesis returns a list of TRUE, FALSE, TRUE.....results. To make those into a list of 1,0,1...results, you take the double negative. In point of fact, there are several mathematical operations that work (multiply by 1, divide by 1 for instance) but the convention is the double negative because it is fast to type.
 
Last edited:
Upvote 0
I think I got it now thanks for expanding on that. Those instances where the date equals a dash (or any string) will count twice as the dash is greater than all dates while the dash is also equal to dash resulting in 2. Adding >0 to the condition as a whole returns a TRUE (1).
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top