Thanks:  0
Likes:  0

# Thread: Countifs and Sumprod not producing same result when they should

1. ## Countifs and Sumprod not producing same result when they should

Here are two formulas that should produce the same result:

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

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.

2. ## Re: Countifs and Sumprod not producing same result when they should

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

4. ## Re: Countifs and Sumprod not producing same result when they should

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)

5. ## Re: Countifs and Sumprod not producing same result when they should

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 -

6. ## Re: Countifs and Sumprod not producing same result when they should

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.

7. ## Re: Countifs and Sumprod not producing same result when they should

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

8. ## Re: Countifs and Sumprod not producing same result when they should

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.

9. ## Re: Countifs and Sumprod not producing same result when they should

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

10. ## Re: Countifs and Sumprod not producing same result when they should

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!