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:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
For whatever reason I am unable to cut and paste the sumproduct formula without the formula being edited by the editor. After the first 164000 there should be a closing bracket and multiply symbol
 
Upvote 0
What's in column B?

In fact, if you could show a few rows of sample data it would be helpful. Fictionalize it if required, but do retain the relationship typical of your real data (if the dates in one column are always before the dates in the other, for example)
 
Last edited:
Upvote 0
Column B are dates only.

Here is All emails date tab
A
BCD
emailFirst ContactWA CreatedWA Created Date
dogs@gmail.com8/13/2014Yes8/13/2014
cats@gmail.com9/20/2014Yes-
sheep@yahoo.com7/9/2012Yes7/9/2012
horses@example.edu1/11/2013Yes-

<tbody>
</tbody>
 
Last edited:
Upvote 0
OK. I was editting my question while you were answering it. If you could show a few rows of sample data it would be helpful. Fictionalize it if required, but do retain the relationship typical of your real data (if the dates in one column are always before the dates in the other, for example). My first impression is you are using SUMPRODUCT differently than I am used to. I never do any multiplication within a SUMPRODUCT, the function is doing the muliplication for me.
 
Upvote 0
Data added now, thanks @gardnertoo. I use sumproduct to filter down a table to arrive at a count of records that match the criteria.

In this case I want to say, in English:
Return the count of records where:
ColB is less than 1/1/14 AND
(ColD is on or after 1/1/14 OR is equal to "-")
 
Last edited:
Upvote 0
This seems to work:
Code:
=SUMPRODUCT(--('all emails date'!$B$3:$B$164000<=data1!$C$2),--('all emails date'!$B$3:$B$164000>0),--('all emails date'!$D$3:$D$164000>=data1!$C$2))
The ">0" portion was for my testing where I didn't fill in all the rows, you may not have empty cells in the B3:B164000 range. Also, it seems that Excel considers the "-" sign to be a date greater than anything, so I didn't need a second section to look for the dash sign.
 
Last edited:
Upvote 0
I think gardnertoo has hit upon the reason for the discrepancy - when you use a comparison in SUMPRODUCT any text value is deemed to be greater than any number, so your SUMPRODUCT formula is double counting the "-" values because they are both ="-" (obviously) and >=data1!C2

COUNTIFS doesn't work like that, when you use a "<" or ">" comparison with a number it only counts numbers

To make SUMPRODUCT work try this version

=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="-") >0))

For the second part by using >0 you can only count each row once

Note: The board here sometimes has trouble displaying > or < symbols because they are misinterpreted as HTML tags - just leave a space next to the open end and they should display OK
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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