+ Reply to Thread
Results 1 to 5 of 5

Struggling with SUMPRODUCT when matching "time between" values

  1. 03-20-2015, 11:46 AM #1
    designergav
    designergav is offline
    Registered User
    Join Date
    03-12-2015
    Location
    marlow, uk
    MS-Off Ver
    Office for Mac 2011
    Posts
    17

    Struggling with SUMPRODUCT when matching "time between" values

    I have this formula:

    =IFERROR(SUMPRODUCT((Table1[Sent Time]>="06:00")*(Table1[Sent Time]<"07:00"),Table1[Open rate (%)])/COUNTIF(Atlassian[Sent Time],">=06:00"),0)

    Which is supposed to add all the values in the [Open rate (%)] column when the time in [Sent Time] is between 6am and 7am then divide by the total matches to give an average.

    The COUNTIF part works as it returns the correct number of matches but I can't figure out the rest. I get no error, just a zero value.

    Can anyone help?

    (I'm using Excel for Mac 2011)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-20-2015, 11:55 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Struggling with SUMPRODUCT when matching "time between" values

    why not just use averageifS()?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 03-20-2015, 11:59 AM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Struggling with SUMPRODUCT when matching "time between" values

    OK, you have nothing that matches your criteria, but this...
    =AVERAGEIFS(Table1[Open rate (%)],Table1[Sent Time],">="&8/24,Table1[Sent Time],"<"&11/24)
    returns 50.85448744

    edit: so does this...
    =IFERROR(SUMPRODUCT((Table1[Sent Time]>=8/24)*(Table1[Sent Time]<11/24),Table1[Open rate (%)])/COUNTIF(Table1[Sent Time],">=06:00"),0)
    Reply With QuoteRegister To Reply
  4. 03-20-2015, 12:02 PM #4
    designergav
    designergav is offline
    Registered User
    Join Date
    03-12-2015
    Location
    marlow, uk
    MS-Off Ver
    Office for Mac 2011
    Posts
    17

    Re: Struggling with SUMPRODUCT when matching "time between" values

    Why not indeed. I wasn't aware of averageifs.

    The syntax is sooooo much simpler.

    =IFERROR(AVERAGEIFS(Table1[Open rate (%)],Atlassian[Sent Time],">=06:00",Table1[Sent Time],"<07:00"),0)

    If only Excel made the syntax for all the different functions standard.

    Thanks for the super speedy reply.
    Last edited by designergav; 03-20-2015 at 12:17 PM.
    Reply With QuoteRegister To Reply
  5. 03-20-2015, 01:07 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Struggling with SUMPRODUCT when matching "time between" values

    Happy to help, thanks for the feedback and kind words

    If only Excel made the syntax for all the different functions standard.
    They pretty much do
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Assign Categories for Time of Day: "Morning", "Afternoon", "Evening"
    By long_shanks in forum Excel General
    Replies: 3
    Last Post: 06-11-2013, 02:59 AM
  2. [SOLVED] Formula needed to display "Pass" or "Fail" if a column contains any values other than "yes
    By andreindy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2013, 05:49 PM
  3. Seperating "colour" and "size" values into seperate columns from the "product name"
    By sy_lavery in forum Excel General
    Replies: 0
    Last Post: 01-09-2013, 06:52 PM
  4. [SOLVED] Data Arrives as Text, struggling to "normalise" it
    By DominicJ in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-23-2012, 10:55 AM
  5. Excel 2007 : Match Values and Return "Present" "Absent" "Per Diem" or remain blank
    By DanBraden in forum Excel General
    Replies: 3
    Last Post: 02-16-2011, 02:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1