+ Reply to Thread
Results 1 to 6 of 6

Adjacent Two Column Addition with Text and Numbers

  1. #1
    Registered User
    Join Date
    07-16-2013
    Location
    NH, USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Adjacent Two Column Addition with Text and Numbers

    I have an employee spreadsheet with various data on it. I need to pull data from two adjacent columns and total just one of them.

    One column(I) has each employees employment status: PT or FT
    The second column(H) that is adjacent has each employees hours worked in a week.

    I would like to total up the hours for all employees by their employment status and then divide each total by 4.

    Total PT Hours per Week/ Month 0
    Total FT Hours per Week/Month 0

    Hopefully my explanation was clear... This is a project to determine my businesses exposure to the new Health Care Plan requirements. Once I get the "PT & FT" totals I will apply them to the governments formula.

    Thanks in advance for any help.

    John
    Attached Files Attached Files
    Last edited by Jrsaint; 07-16-2013 at 12:21 PM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,707

    Re: Beyond my paygrade!

    i posted before I notice the title here , and would advise
    You would need to EDIT and change your title
    have a read if rule 1 here
    http://www.excelforum.com/forum-rule...rum-rules.html

    Your post title should accurately and concisely describe your problem, not your anticipated solution.
    I know the moderators do monitor and post for people to change title

    i assume you have

    Employee -- Status -- Hours

    1 -- PF -- 2
    1 -- PF -- 5
    1 -- PF -- 6
    1 -- FT -- 5
    1 -- FT -- 8
    1 -- FT -- 12

    assuming above in column A, B & C

    OR would you only have 2 entries per employee

    a pivot table would work

    Or you could sumifs( )

    Employee Total PT Hours per Week/ Month ---- SUMIFS(C:C, A:A, "Employee ref", B:B, "PT") /4
    Employee Total FT Hours per Week/ Month ---- SUMIFS(C:C, A:A, "Employee ref", B:B, "FT") /4
    Last edited by etaf; 07-16-2013 at 10:58 AM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    07-16-2013
    Location
    NH, USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Beyond my paygrade!

    Hey Wayne,

    Thanks for the heads up on the rules. I'm a noob who evidently does not know how to read the rules. I adjusted the post title so we should be all set for now. Thanks again. Your response assumptions were off just a little. I'm sure my explanation was the cause. Let me try to explain it better.

    The employee name, etc. is really not part of the data I am trying to gather. I would like to first identify "PT" and "FT" which is located in column (I) and has data in rows I2-I57. Just in front of Row I, I have average hours worked located in column (H). This column has the average hours worked in a week by each employee and is represented in rows H2-H57.

    What I would like to pull out is the total of "PT" hours and "FT" hours each week and multiply each by 52 weeks and divide by 12 mos. This will give me a true monthly avg of "PT" and "FT" hours for the insertion to the Govt. formula.

    Hopefully, this is a better explanation. Should I try to attach the Worksheet?

    John

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,707

    Re: Adjacent Two Column Addition with Text and Numbers

    Should I try to attach the Worksheet?
    that would be good
    But if all you want is a Sum of FT and PT

    =Sumif(I2:I57,"PT",H2:H57) *52/12
    =Sumif(I2:I57,"FT",H2:H57) *52/12

  5. #5
    Registered User
    Join Date
    07-16-2013
    Location
    NH, USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Adjacent Two Column Addition with Text and Numbers

    Wayne,

    You are the man! Everything works great. I have the data I need to proceed. Thanks a ton for your help and quick response. Additionally, thanks for the title posting tip.

    John

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,707

    Re: Adjacent Two Column Addition with Text and Numbers

    you more than welcome, and thanks for the understanding on the titles - mods and admin usually pick those up and then we cannot answer until modified

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1