+ Reply to Thread
Results 1 to 16 of 16

Sumproduct Help

  1. #1
    Registered User
    Join Date
    08-06-2014
    Location
    NY, NY
    MS-Off Ver
    2013
    Posts
    10

    Sumproduct Help

    Hi,

    I am currently working in a file that I am going to post a sample of. My problem is that I need a count of cars by ID# given a certain time period. So in order to set the date range I am using the formula =SUMPRODUCT((Sheet2!A:A>=$A$1)*(Sheet2!A:A<=EOMONTH($A$1,0))). Where A1 is August 2014.

    When I run this formula I get 9, which the correct number of instances that August 2014 is listed on my date list in Sheet2. After this I need a sum of Column C in sheet2 based off the ID# in sheet 1.

    Sorry if my explanation is not very good. Sample Problem.xlsxI have attached a sample. Please let me know if there are questions and I can clarify as best as I can.

    Thanks!!

    Jacob

  2. #2
    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,917

    Re: Sumproduct Help

    Not sure if I have the dates right, but try this method instead...
    =COUNTIFS(Sheet2!$B:$B,Sheet1!$A3,Sheet2!$A:$A,">="&Sheet1!A$1,Sheet2!$A:$A,"<"&EDATE(A$1,4))
    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

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Sumproduct Help

    Does this work for you?

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Sumproduct Help

    Try this one

    =COUNTIFS(Sheet2!$B$2:$B$1000,$A3,Sheet2!$A$2:$A$1000,">="&A$1,Sheet2!$A$2:$A$1000,"<="&EOMONTH(A$1,0))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    08-06-2014
    Location
    NY, NY
    MS-Off Ver
    2013
    Posts
    10

    Re: Sumproduct Help

    Ron, this gives me the correct total, but it is not broken out by ID#.

  6. #6
    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,917

    Re: Sumproduct Help

    Did you try the suggested countifS()?

  7. #7
    Registered User
    Join Date
    08-06-2014
    Location
    NY, NY
    MS-Off Ver
    2013
    Posts
    10

    Re: Sumproduct Help

    Yes and when I use it I get 21 as the total car count when the total should be 17.

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Sumproduct Help

    See post #4
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-06-2014
    Location
    NY, NY
    MS-Off Ver
    2013
    Posts
    10

    Re: Sumproduct Help

    I am looking for the sum of the cars in column C. So the total should be 17 for August 2014 because there are 9 ID's totaling 17 cars.

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sumproduct Help

    To sum one column based on multiple conditions in some columns SUMIFS is usually the most efficient approach - to sum column C for August 2014 try this version:

    =SUMIFS(Sheet2!C:C,Sheet2!A:A,">="&$A$1,Sheet2!A:A,"<="&EOMONTH($A$1,0))

    That gives me 17
    Audere est facere

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Sumproduct Help

    This will give 17

    =COUNTIFS(Sheet2!$B$2:$B$1000,$A3,Sheet2!$A$2:$A$1000,">="&A$1,Sheet2!$A$2:$A$1000,"<="&EOMONTH(A$1,2))

  12. #12
    Registered User
    Join Date
    08-06-2014
    Location
    NY, NY
    MS-Off Ver
    2013
    Posts
    10

    Re: Sumproduct Help

    Quote Originally Posted by daddylonglegs View Post
    To sum one column based on multiple conditions in some columns SUMIFS is usually the most efficient approach - to sum column C for August 2014 try this version:

    =SUMIFS(Sheet2!C:C,Sheet2!A:A,">="&$A$1,Sheet2!A:A,"<="&EOMONTH($A$1,0))

    That gives me 17
    Is it possible to get the total based on ID#. So instead of 17 for each of the ID#'s I would for example get 3 for ID# A0308 becasue of Aug 21 there was 1 car and on August 28th there were 2 cars.

  13. #13
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sumproduct Help

    Yes, just add another condition, i.e. this formula in B3 copied down

    =SUMIFS(Sheet2!C:C,Sheet2!B:B,A3,Sheet2!A:A,">="&$A$1,Sheet2!A:A,"<="&EOMONTH($A$1,0))

  14. #14
    Registered User
    Join Date
    08-06-2014
    Location
    NY, NY
    MS-Off Ver
    2013
    Posts
    10

    Re: Sumproduct Help

    Quote Originally Posted by AlKey View Post
    This will give 17

    =COUNTIFS(Sheet2!$B$2:$B$1000,$A3,Sheet2!$A$2:$A$1000,">="&A$1,Sheet2!$A$2:$A$1000,"<="&EOMONTH(A$1,2))
    I get 17 as well. This issue that I am having is that for ID# 33965 I only get 1, but if I look at the data in sheet2 it says there are 3 cars.

  15. #15
    Registered User
    Join Date
    08-06-2014
    Location
    NY, NY
    MS-Off Ver
    2013
    Posts
    10

    Re: Sumproduct Help

    Quote Originally Posted by daddylonglegs View Post
    Yes, just add another condition, i.e. this formula in B3 copied down

    =SUMIFS(Sheet2!C:C,Sheet2!B:B,A3,Sheet2!A:A,">="&$A$1,Sheet2!A:A,"<="&EOMONTH($A$1,0))
    That solves it. Thank you everyone who helped!

  16. #16
    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,917

    Re: Sumproduct Help

    Happy to help and thanks for the feedback

    (I did mention that I might have my dates wrong, so that may be the reason for the discrepancy)

+ 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. Count SUMPRODUCT Members / Average of SUMPRODUCT
    By Shingaru in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2014, 03:59 PM
  2. Replies: 1
    Last Post: 05-19-2012, 02:54 AM
  3. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  4. Replies: 6
    Last Post: 03-09-2011, 08:01 AM
  5. Using a SumProduct Count to find a SumProduct Total?
    By XL021710 in forum Excel General
    Replies: 3
    Last Post: 02-18-2010, 08:31 AM

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