+ Reply to Thread
Results 1 to 10 of 10

Formual with 2 Criteria using SUMIF and a date range

  1. 03-13-2013, 05:33 PM #1
    Michael_GR
    Michael_GR is offline
    Registered User
    Join Date
    08-10-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Post Formual with 2 Criteria using SUMIF and a date range

    Good day to all. The bellow formula is part of my equation that does work. The missing pc is a date range in columns B19-b5017. We put an invoice date in the format of 3/13/2013. I want to have a return of information in the attached formula with dates from the month beginning on 1 and ending the last day of the month, IE 3/1/2013 - 3/31/2013. I am inclined to believe I am looking for a SUMIF AND but I can't seem to narrow this down.

    Generally speaking, I am adding dollars up in 1 column (listed formula) that is for each month listed seperately in fields for the sales people.

    =SUM(IF(A19:A5017="SM",E19:E5017))

    Again, this formula works great. I just need the date part.

    I appreciate any and all assistance.
    Michael
    Reply With QuoteRegister To Reply
  2. 03-13-2013, 05:54 PM #2
    Harribone
    Harribone is offline
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Formual with 2 Criteria using SUMIF and a date range

    Change the formula to this:

    Please Login or Register  to view this content.
    Say thanks, click *
    Reply With QuoteRegister To Reply
  3. 03-13-2013, 06:09 PM #3
    Michael_GR
    Michael_GR is offline
    Registered User
    Join Date
    08-10-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formual with 2 Criteria using SUMIF and a date range

    Ok, let me ask, where do I put the date range in. Listed are 2 columns obviously. Columns A hold the initials of the sales people, Column E holds the current sales numbers.

    So, what is the best syntax to add a date range say in column b. I can also send you the spreadsheet if that would help. I just didnt want the world to have access too it.

    Mike
    Reply With QuoteRegister To Reply
  4. 03-13-2013, 06:33 PM #4
    Harribone
    Harribone is offline
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Formual with 2 Criteria using SUMIF and a date range

    Depends on the condition of the date range in Col B, if you are looking for results between 2 dates or greater than a date etc.
    Can you upload a copy of your file with false data so you don't share anything you don't want to make public. And can you state where the result cell is and an example answer manually entered please so I can make sure I give you the right formula to suit the job.
    Last edited by Harribone; 03-13-2013 at 06:36 PM.
    Reply With QuoteRegister To Reply
  5. 03-13-2013, 06:49 PM #5
    Michael_GR
    Michael_GR is offline
    Registered User
    Join Date
    08-10-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formual with 2 Criteria using SUMIF and a date range

    I am trying to find out how to attached a file.
    Reply With QuoteRegister To Reply
  6. 03-13-2013, 06:54 PM #6
    Harribone
    Harribone is offline
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Formual with 2 Criteria using SUMIF and a date range

    At the bottom of the thread where you can post a quick reply there is a button called "Go Advanced". Click this and you can manage attachments from there. Upload a file and click Insert Inline once uploaded to add this to a post
    Reply With QuoteRegister To Reply
  7. 03-13-2013, 06:57 PM #7
    Michael_GR
    Michael_GR is offline
    Registered User
    Join Date
    08-10-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formual with 2 Criteria using SUMIF and a date range

    Ok, I have added the file.

    Hopefully I have detailed the information so it doesn't confuse you.

    Imagine this.

    Each month I would be able to display Column A (person), The date range (month of March) with a total dollars that is listed each day. Again, I can arrange the formula for Sales dollars verses Profit dollars. I just can't get the syntax for the date range to be a part of the formula.

    Mike
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-13-2013, 07:16 PM #8
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formual with 2 Criteria using SUMIF and a date range

    Maybe something like this will work for you:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by newdoverman; 03-13-2013 at 07:23 PM.
    <---------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
    Reply With QuoteRegister To Reply
  9. 03-13-2013, 07:17 PM #9
    Harribone
    Harribone is offline
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Formual with 2 Criteria using SUMIF and a date range

    SUMIFSDATERANGE.xlsx

    In the attached file I have included a date range in two cells J25 and J26, this is the first day of march and the last day of march.

    So for Shad ("SM") the value for sales is calculated from the data in A:F when A = "SM" and B is >= J26 (start of month) and B <= J27 (end of month).
    The same is done for the Dollars total.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Obviously you can change the location of the date range cells, I just chucked them in there for ease.

    EDIT: Instead of referencing the date range cells you can modify the formulas and type in the date ranges yourself, I did it that way so you can simply change the range in one place and therefore update everyone's info in one go (once they have formulas set up).
    Last edited by Harribone; 03-13-2013 at 07:20 PM.
    Reply With QuoteRegister To Reply
  10. 03-13-2013, 07:53 PM #10
    Michael_GR
    Michael_GR is offline
    Registered User
    Join Date
    08-10-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formual with 2 Criteria using SUMIF and a date range

    You are awesome. Works like a charm. I read about SUMPRODUCT but didnt think it applied to me. I truly thank you.
    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)

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