+ Reply to Thread
Results 1 to 13 of 13

Sumproduct with Left Function

  1. #1
    Registered User
    Join Date
    07-31-2019
    Location
    Glasgow
    MS-Off Ver
    2016
    Posts
    2

    Sumproduct with Left Function

    Hi

    Is is possible to combine the sumproduct and left function together?

    I am creating a reference cell that will give me the total of ID's which were created this month.

    Below would be an example table:

    ID Creation Date
    8.88 01/01/2019
    8.89 01/01/2019
    8.9 01/07/2019
    1.1 01/07/2019
    1.2 01/06/2019
    1.3 01/05/2019
    1.4 01/04/2019
    5 01/07/2019
    5.1 01/07/2019
    5.2 01/06/2019
    5.3 01/06/2019


    I have the function which tells me how many were created this month =SUMPRODUCT(--(MONTH(N7:N17)=MONTH(TODAY()))) & how many start with e.g. =SUMPRODUCT(--(LEFT(M7:M17,1)="8"))

    I would like to know if if it is possible to combine these two functions together? But as an 'AND' rather than 8 the two results together.

    e.g. i would like a formula that would be able to give me - For this month (July 2019) - there was 1 ID created this month for ID's begin with 8

    I would need to include the left function to pull out the 1st digit (which will always run from 1 - 9)

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Sumproduct with Left Function

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually).

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    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
    40,398

    Re: Sumproduct with Left Function

    Welcome to the forum.

    =SUMPRODUCT(--(MONTH(N7:N17)=MONTH(TODAY())*(--(LEFT(M7:M17,1)="8"))))
    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.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,439

    Re: Sumproduct with Left Function

    Try, untested:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    07-31-2019
    Location
    Glasgow
    MS-Off Ver
    2016
    Posts
    2

    Re: Sumproduct with Left Function

    As simple as that. Thanks very much - that's perfect!

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: Sumproduct with Left Function

    Isn't this simply

    =SUMPRODUCT(--(MONTH(N7:N17)=MONTH(TODAY()))*(LEFT(M7:M17,1)="8"))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  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
    40,398

    Re: Sumproduct with Left Function

    Isn't that what I said in post #3?

  8. #8
    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
    40,398

    Re: Sumproduct with Left Function

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  9. #9
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: Sumproduct with Left Function

    Quote Originally Posted by AliGW View Post
    Isn't that what I said in post #3?
    Who me?
    Yeah got delayed with a phone call so didnt see your reply till I finished posting mine after the call.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,439

    Re: Sumproduct with Left Function

    In theory, you don't need the double negative AND the multiplication operations.

    The double negative, as an arithmetic function, converts the string of TRUE/FALSE to ones and zeroes. SUMPRODUCT then multiplies the arrays. The asterisk multiplication should do that in one go but Iím never convinced. My preference is the double negative.

  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
    40,398

    Re: Sumproduct with Left Function

    Trevor - that's a good point.

    Special-K - who, you?

  12. #12
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: Sumproduct with Left Function

    Quote Originally Posted by AliGW View Post
    Special-K - who, you?
    I've no idea half the time (I'll cut down on the beer)!

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,439

    Re: Sumproduct with Left Function

    As simple as that. Thanks very much - that's perfect!
    Not sure which answer that applied to but, no worries, you have a solution.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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 using Left and Right
    By micope21 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-16-2019, 08:08 AM
  2. [SOLVED] using left function in sumproduct
    By steeler11111 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-30-2016, 10:44 AM
  3. [SOLVED] using left function in sumproduct
    By steeler11111 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-30-2016, 10:22 AM
  4. [SOLVED] Sumproduct and Left
    By siqqboy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-11-2014, 01:39 PM
  5. SumProduct with using Left() Function
    By rmb623 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-16-2013, 05:53 AM
  6. Excel 2007 : LEFT With SUMPRODUCT
    By erik.kirby in forum Excel General
    Replies: 5
    Last Post: 02-10-2011, 03:08 PM
  7. Left and Sumproduct
    By Alexball in forum Excel General
    Replies: 1
    Last Post: 08-09-2006, 07:04 AM

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