+ Reply to Thread
Results 1 to 10 of 10

SUMIFS figuring hourly rate based on date and amount made

  1. #1
    Registered User
    Join Date
    03-02-2020
    Location
    Topeka Kansas
    MS-Off Ver
    2016
    Posts
    6

    SUMIFS figuring hourly rate based on date and amount made

    I've been trying to get my head around this for to long and it's time to ask for help. I am new here, but with a new found excitement for Excel because of this project. I've embarked on this project to help my wife keep track of her customers, sales, expenses and other work related items. I've been able to google and figure out the formulas and get the data to answer questions we have but recently she asked if I could have it tell her how much per hour she is making based on the date. So I created her a column that houses the Hours worked.

    In the Sales sheet I'm trying to SUMIFS the date column (B), the amount (E), and divide that number by the Hours (A). I've come up with =SUMIFS(E7:E10000, B7:B10000,"/"&L3,A7:A10000,L5) that doesn't work. Anyone have any ideas on how to make this happen? Maybe SUMIFS isn't the correct formula to do what I'm trying to do? I've attached the workbook. Any help is greatly appreciated.
    Attached Files Attached Files
    Last edited by rothe31; 03-04-2020 at 04:16 PM.

  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: SUMIFS figuring hourly rate based on date and amount made

    1. You dont need to use = in a sumifs, its not wrong, just unnecessary...
    =SUMIFS(E7:E9987,B7:B9987,"="&L3)
    is the same as
    =SUMIFS(E7:E9987,B7:B9987,L3)

    2. You cannot directly use mathematical operators like hat in a SUMIFS, all it does is simply add values in 1 column, based on criteria found in other columns.

    If you were to do this calc manually, how would you do it?
    Would this work?
    =AVERAGEIFS(E7:E9987, B7:B9987,L3)
    (gives 1.858)
    what exactly are you trying to do with your formula...
    =SUMIFS(E7:E9987, B7:B9987,"/"&L3,A7:A9987,L5)
    I get the 1st part, but A7:A9987,L5? Seeing as this is a sum of certain values in E, that value (L5) will never be found in col B - which is dates anyway
    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 bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: SUMIFS figuring hourly rate based on date and amount made

    In case of : total hour of 29-Feb with 7h/each= 7*6=42 hours, then hourly rate=
    Please Login or Register  to view this content.
    =$11.15/42=$0.26
    Does it meet?
    Quang PT

  4. #4
    Registered User
    Join Date
    03-02-2020
    Location
    Topeka Kansas
    MS-Off Ver
    2016
    Posts
    6

    Re: SUMIFS figuring hourly rate based on date and amount made

    moved this to be a quoted reply
    Last edited by rothe31; 03-03-2020 at 10:52 AM.

  5. #5
    Registered User
    Join Date
    03-02-2020
    Location
    Topeka Kansas
    MS-Off Ver
    2016
    Posts
    6

    Re: SUMIFS figuring hourly rate based on date and amount made

    moved this to be a quoted reply
    Last edited by rothe31; 03-03-2020 at 10:52 AM.

  6. #6
    Registered User
    Join Date
    03-02-2020
    Location
    Topeka Kansas
    MS-Off Ver
    2016
    Posts
    6

    Re: SUMIFS figuring hourly rate based on date and amount made

    Quote Originally Posted by bebo021999 View Post
    In case of : total hour of 29-Feb with 7h/each= 7*6=42 hours, then hourly rate=
    Please Login or Register  to view this content.
    =$11.15/42=$0.26
    Does it meet?
    Sorry for not explaining the Hours column enough, but to keep it as simple as I can she has multiple appointments over the course of the day so my thought was to just enter the hours she worked across from the first appointment of the day based on the date. For example B8 shows 2/29/2020 and in A8 I have 7. That 7 hours is the entire day she worked all appointments on 2/29/2020. I think you're on to something with your formula but not sure how to tweak it to get $11.15/7=1.59. Essentially =SUM(E8:E13) and then =SUM(E18/A8) [E18 is where I did the test sum]

  7. #7
    Registered User
    Join Date
    03-02-2020
    Location
    Topeka Kansas
    MS-Off Ver
    2016
    Posts
    6

    Re: SUMIFS figuring hourly rate based on date and amount made

    Quote Originally Posted by FDibbins View Post
    1. You dont need to use = in a sumifs, its not wrong, just unnecessary...
    =SUMIFS(E7:E9987,B7:B9987,"="&L3)
    is the same as
    =SUMIFS(E7:E9987,B7:B9987,L3)

    2. You cannot directly use mathematical operators like hat in a SUMIFS, all it does is simply add values in 1 column, based on criteria found in other columns.

    If you were to do this calc manually, how would you do it?
    Would this work?
    =AVERAGEIFS(E7:E9987, B7:B9987,L3)
    (gives 1.858)
    what exactly are you trying to do with your formula...
    =SUMIFS(E7:E9987, B7:B9987,"/"&L3,A7:A9987,L5)
    I get the 1st part, but A7:A9987,L5? Seeing as this is a sum of certain values in E, that value (L5) will never be found in col B - which is dates anyway
    The Average works and gets a close number but she likes to know exactly what she made. Amount made divided by hours worked that day. But I see what your explaining that SUMIFS can't do what I'm looking to do. I had a feeling I was on the wrong path since I was doing more then math on a one group of numbers based on date. I'm glad I broke down and asked for help. This is awesome seeing what some solutions look like. Thank you for your time answering with a potential solution!

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: SUMIFS figuring hourly rate based on date and amount made

    Quote Originally Posted by rothe31 View Post
    Sorry for not explaining the Hours column enough, but to keep it as simple as I can she has multiple appointments over the course of the day so my thought was to just enter the hours she worked across from the first appointment of the day based on the date. For example B8 shows 2/29/2020 and in A8 I have 7. That 7 hours is the entire day she worked all appointments on 2/29/2020. I think you're on to something with your formula but not sure how to tweak it to get $11.15/7=1.59. Essentially =SUM(E8:E13) and then =SUM(E18/A8) [E18 is where I did the test sum]
    With this:
    Please Login or Register  to view this content.
    returns 1.59 as per your expectation.

  9. #9
    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: SUMIFS figuring hourly rate based on date and amount made

    Happy to help, but as I said, can you show some expected answers, so we know what to aim at?

  10. #10
    Registered User
    Join Date
    03-02-2020
    Location
    Topeka Kansas
    MS-Off Ver
    2016
    Posts
    6

    Re: SUMIFS figuring hourly rate based on date and amount made

    Quote Originally Posted by FDibbins View Post
    Happy to help, but as I said, can you show some expected answers, so we know what to aim at?
    Sure, I will upload another document with how I figured out how to get around it that I came up with yesterday. It may be the best or maybe the only way to get what I'm looking to do?

    Notice I group Hours worked by merging the cells together. Hours worked is for that day. so A:7-A11 is one day and was 5 hours of work. Then I'm garbing that number and placing it into cell O5 =SUMIFS(A7:A10000,B7:B10000,"="&O2). Then I'm doing the math of dividing daily work total and hours worked =SUM(O3/O5). I almost like that idea because in a glance without having to scroll I can see how much made in total and how many hours were worked in total for that day which then gives me the rate at which she was making per hour for that day. IDK maybe this sheet can help someone else because I wish I would have found something like this a few weeks ago when I started this project for my wife.

    Essentially, if you change the dates it will give you the answers based on those dates or date ranges on the Sales worksheet.

+ 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] normalizing hours based on new hourly rate
    By TheVolkinator in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-30-2019, 01:56 PM
  2. Replies: 3
    Last Post: 06-13-2019, 05:07 PM
  3. Replies: 6
    Last Post: 01-18-2019, 03:29 PM
  4. [SOLVED] need help figuring out hourly rate with this indirect lookup
    By D8THSTAR in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-07-2018, 05:21 PM
  5. Replies: 11
    Last Post: 02-22-2016, 02:39 PM
  6. Replies: 11
    Last Post: 07-12-2015, 08:50 PM
  7. Replies: 1
    Last Post: 02-28-2014, 02: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