+ Reply to Thread
Results 1 to 20 of 20

SUMPRODUCT with date range and conditions

  1. #1
    Registered User
    Join Date
    07-17-2019
    Location
    Worcestershire
    MS-Off Ver
    2016
    Posts
    9

    SUMPRODUCT with date range and conditions

    Hi
    I have a CRM spreadsheet where I am trying to create an historical calendar record of all productivity and sales per calendar month.

    This is where I have got to:
    =SUMPRODUCT(--('Communication Log'!$A$6:$A57<=B44),--('Communication Log'!$A$6:$A57>=A44),--('Communication Log'!$I17:$I57=1))
    but i get #VALUE

    Also tried
    =SUMPRODUCT(('Communication Log'!$A$6:$A57>=DATE(7,1,2019))*('Communication Log'!$A$6:$A57<=DATE(7,31,2019))*('Communication Log'!$I17:$I57=3))
    but I get #N/A

    I am working on the overview tab. Column A is the start of the date range (1/7/2019) and column B is the end (31/7/2019)
    Data is held on the Communication Log sheet with the dates of entry in column A: and the criteria (type of productivity) in Column I eg -1=call, 2= email, 3 = meeting etc.

    The question i am trying to answer is how much productivity, per type, was carried out between 1/7/19 and 31/7/2019.

    Having looked on line I have tried various options eg: with " " either side of <= and replacing -- with * but errors appear.

    Can anyone guide me on this?
    Ruth

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: SUMPRODUCT with date range and conditions

    I think your issue here is...
    'Communication Log'!$I17:$I57=3

    Which does not match the dimension of your Date check column ('Communication Log'!$A$6:$A57).

    Try changing it to 'Communication Log'!$I$6:$I57=3
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    07-17-2019
    Location
    Worcestershire
    MS-Off Ver
    2016
    Posts
    9

    Re: SUMPRODUCT with date range and conditions

    Hi, thanks for your reply.

    That made sense, tried, but it didn't work

    However I have tried this =SUMPRODUCT(--('Communication Log'!$A$6:$A46>=$A$44),--('Communication Log'!$A$6:$A46<=$B$44),--('Communication Log'!$I6:$I46=1)) and its delivering the answers

  4. #4
    Registered User
    Join Date
    07-17-2019
    Location
    Worcestershire
    MS-Off Ver
    2016
    Posts
    9

    Re: SUMPRODUCT with date range and conditions HELP please!

    I have attached the spreadsheet I am working on.

    The tab is the Dashboard tab and I am trying to populate F44, G44 and H44 by bringing in the sum of the criteria from the Opportunity Tracker.

    Thanks to anyone who can help
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: SUMPRODUCT with date range and conditions

    What result are you expecting in F44 and why?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Registered User
    Join Date
    07-17-2019
    Location
    Worcestershire
    MS-Off Ver
    2016
    Posts
    9

    Re: SUMPRODUCT with date range and conditions

    Hi
    I need to see a sum of all the entries which have been 'Won'. So sum of Opportunity Tracker/T within the date range set on Dashboard A:B where Opportunity Tracker/W = 9 and 10

    Hope that explains a bit clearer

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: SUMPRODUCT with date range and conditions

    And what result are you expecting to see in F44, please?

  8. #8
    Registered User
    Join Date
    07-17-2019
    Location
    Worcestershire
    MS-Off Ver
    2016
    Posts
    9

    Re: SUMPRODUCT with date range and conditions

    Sorry Ali.

    At the moment it would be £18765

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: SUMPRODUCT with date range and conditions

    OK - thanks. I'll have another look.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: SUMPRODUCT with date range and conditions

    I'm struggling to see what's wrong here. Apart from the fact that you did not include the range with the amounts in the SUMPRODUCT (column T), which I have now added in, it is not finding matches.

    Your dates look OK and I have tested the column W criterion with 9 and "9" - neither is returning results.

    There will be something obvious somewhere, but I haven't found it yet.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: SUMPRODUCT with date range and conditions

    I give in! The SUMPRODUCT on this occasion has me beaten.

    However, this works:

    =SUMIFS('Opportunity Tracker'!T4:T544,'Opportunity Tracker'!F4:F544,">="&A44,'Opportunity Tracker'!F4:F544,"<="&B44,'Opportunity Tracker'!W4:W544,9)

  12. #12
    Registered User
    Join Date
    07-17-2019
    Location
    Worcestershire
    MS-Off Ver
    2016
    Posts
    9

    Re: SUMPRODUCT with date range and conditions

    I'm glad its not just me! Yes SUMIFS works - thank you Ali, much appreciated.

    One last question - if I want to read status code 10 as well as the 9, how would I add that on?
    If it helps, another option is to sum Opportunity Tracker/Q as this segregates codes 9 and 10 from the others. I have only just thought of this but hoping it may make things easier?

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: SUMPRODUCT with date range and conditions

    Try this:

    =SUMIFS('Opportunity Tracker'!T4:T544,'Opportunity Tracker'!F4:F544,">="&A44,'Opportunity Tracker'!F4:F544,"<="&B44,'Opportunity Tracker'!W4:W544,">="&9,'Opportunity Tracker'!W4:W544,"<="&10)
    Last edited by AliGW; 07-18-2019 at 09:58 AM. Reason: Missed """" in formula!

  14. #14
    Registered User
    Join Date
    07-17-2019
    Location
    Worcestershire
    MS-Off Ver
    2016
    Posts
    9

    Re: SUMPRODUCT with date range and conditions

    Thanks Ali,

    Came up with an error so I have amended to read ">="&9 and the same for 10 and the answer remains the same, which is right. Yey!

    I am pretty new to these formulas and not sure what purpose the & does or why we have to use " " either side of <=. Can you enlighten me?

  15. #15
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: SUMPRODUCT with date range and conditions

    Here's sumproduct version. For F44.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    However, SUMIFS is much more efficient formula (i.e. it will automatically shrink down to used range even when you use entire column etc).

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: SUMPRODUCT with date range and conditions

    CK76 - have you tested the SUMPRODUCT? That exact formula failed for me on Ruth's data.

  17. #17
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: SUMPRODUCT with date range and conditions

    Here's the sheet where I used the formula.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    07-17-2019
    Location
    Worcestershire
    MS-Off Ver
    2016
    Posts
    9

    Re: SUMPRODUCT with date range and conditions

    Ah, right, Thanks CK76. SUMPRODUCT does seem to be more convoluted although I'm sure it has benefits on other tasks.

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: SUMPRODUCT with date range and conditions

    Hmmmm ... I'm baffled. It is working now, yet wasn't when I tried it.. Oh, well - as you say, SUMIFS is more efficient.

  20. #20
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: SUMPRODUCT with date range and conditions

    I'm sure it has benefits on other tasks.
    When you need to perform transformation/extraction on range before you perform checks, SUMIFS can't be used (unless helper column is added).
    That's when SUMPRODUCT can be used.
    Ex:
    =SUMPRODUCT((LEFT(Range, 3)="WIN")*(Range<=Date),(SumRange))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sumproduct with date range?
    By tche misere in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-03-2014, 01:13 AM
  2. Looking up on 2 conditions, 1 being a date range
    By smitha00 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-19-2013, 06:04 PM
  3. getting the min date and max date from a range after meeting multiple conditions
    By lynn.plazo@gmail.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-11-2013, 04:42 PM
  4. [SOLVED] SUMPRODUCT issue with summing based off date conditions
    By guitargod7277 in forum Excel General
    Replies: 6
    Last Post: 06-06-2012, 05:22 PM
  5. Replies: 8
    Last Post: 05-14-2012, 02:44 PM
  6. [SOLVED] Sumproduct with date range
    By ermeko in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-16-2006, 12:20 PM
  7. SUMPRODUCT using date range
    By was in forum Excel General
    Replies: 7
    Last Post: 02-22-2005, 08:23 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1