# Countifs and Sumprod not producing same result when they should

#### doug firr

##### Board Regular
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### doug firr

##### Board Regular
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

<data1!c\$2)*
<data1!c\$2)*

</data1!c\$2)*
</data1!c\$2)*

#### gardnertoo

##### Well-known Member
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:

#### doug firr

##### Board Regular
Column B are dates only.

 Here is All emails date tab A B C D email First Contact WA Created WA Created Date dogs@gmail.com 8/13/2014 Yes 8/13/2014 cats@gmail.com 9/20/2014 Yes - sheep@yahoo.com 7/9/2012 Yes 7/9/2012 horses@example.edu 1/11/2013 Yes -

<tbody>
</tbody>

Last edited:

#### gardnertoo

##### Well-known Member
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.

#### doug firr

##### Board Regular
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:

#### gardnertoo

##### Well-known Member
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:

#### barry houdini

##### MrExcel MVP
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

#### gardnertoo

##### Well-known Member
any text value is deemed to be greater than any number
Good to know, I can see places in my own sheets where I might take advantage of that. I did not know that!

Replies
1
Views
157
Replies
0
Views
174
Replies
5
Views
152
Replies
1
Views
268
Replies
4
Views
201

1,191,670
Messages
5,987,954
Members
440,121
Latest member
eravella

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

### Which adblocker are you using?

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

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