Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by doug firr; Sep 23rd, 2014 at 02:53 PM. Reason: site keeps fiddling with my formula

  2. #2
    Board Regular
    Join Date
    Mar 2011
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs and Sumprod not producing same result when they should



  4. #4
    Board Regular gardnertoo's Avatar
    Join Date
    Jul 2007
    Location
    Goldendale, WA
    Posts
    938
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)
    Last edited by gardnertoo; Sep 23rd, 2014 at 03:28 PM.
    Gardnertoo: I just like to smile! Smiling's my favorite.
    Boss: Make work your favorite. That's your new favorite.

    Home: Windows 7, Excel 2013
    Previous Employer: Windows XP and Windows 7, Excel 2003 and 2007 and 2010
    Current Employer: Google Sheets

  5. #5
    Board Regular
    Join Date
    Mar 2011
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 -
    Last edited by doug firr; Sep 23rd, 2014 at 03:32 PM. Reason: added sample data

  6. #6
    Board Regular gardnertoo's Avatar
    Join Date
    Jul 2007
    Location
    Goldendale, WA
    Posts
    938
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Gardnertoo: I just like to smile! Smiling's my favorite.
    Boss: Make work your favorite. That's your new favorite.

    Home: Windows 7, Excel 2013
    Previous Employer: Windows XP and Windows 7, Excel 2003 and 2007 and 2010
    Current Employer: Google Sheets

  7. #7
    Board Regular
    Join Date
    Mar 2011
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 "-")
    Last edited by doug firr; Sep 23rd, 2014 at 03:39 PM.

  8. #8
    Board Regular gardnertoo's Avatar
    Join Date
    Jul 2007
    Location
    Goldendale, WA
    Posts
    938
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by gardnertoo; Sep 23rd, 2014 at 04:08 PM. Reason: Typo in formula
    Gardnertoo: I just like to smile! Smiling's my favorite.
    Boss: Make work your favorite. That's your new favorite.

    Home: Windows 7, Excel 2013
    Previous Employer: Windows XP and Windows 7, Excel 2003 and 2007 and 2010
    Current Employer: Google Sheets

  9. #9
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    Board Regular gardnertoo's Avatar
    Join Date
    Jul 2007
    Location
    Goldendale, WA
    Posts
    938
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!
    Gardnertoo: I just like to smile! Smiling's my favorite.
    Boss: Make work your favorite. That's your new favorite.

    Home: Windows 7, Excel 2013
    Previous Employer: Windows XP and Windows 7, Excel 2003 and 2007 and 2010
    Current Employer: Google Sheets

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •