+ Reply to Thread
Results 1 to 8 of 8

Applying multiple formulas to multiple columns

  1. #1
    Registered User
    Join Date
    01-10-2019
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    4

    Applying multiple formulas to multiple columns

    I am very new to excel and am teaching myself "OJT". In an attempt to better track overtime by position, by hour, and by multiple reasons for overtime, I began creating this sheet. I have it nearly exactly as i want it, but cannot apply my formulas to other cells. I need to be able to input multiple letters that will track frequency by generating an updated total (if that makes sense). Cells B54 through B66 have the formulas I need. Currently they only apply to D4. I would like to apply them to all columns titled "code". Can and how is this possible?

    Thanks in Advance!
    Attached Files Attached Files
    Last edited by Ascender; 01-10-2019 at 06:10 PM.

  2. #2
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office Professionsl Plus 2016- work; 2012 for Mac at home
    Posts
    5,027

    Re: So close, yet so far

    In my opinion your best option is to reload the sheet with some examples in the different code areas ALONG WITH expected results.
    Last edited by Sambo kid; 01-11-2019 at 06:30 AM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam C

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    46,917

    Re: Applying multiple formulas to multiple columns

    (Thanks for the title change )

    I agree that some sample data - and expected outcome - would help a lot.

    My understanding is that you want a count of each reason code, and for each position, correct?

    Also...
    what are you trying to do with the formulas in B54:B66?
    your SUM formula...=SUM(B10,E10,H10,K10,N10,Q10,T10,)...could be shortened to just =SUM(B10:T10). I doubt you would have values in Reason or Code in that row?
    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

  4. #4
    Registered User
    Join Date
    01-10-2019
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Applying multiple formulas to multiple columns

    FDibbins,
    Correct. For each position, each day, I would like to code overtime reasons A thru M (some days may have multiple reasons for the same position), and in turn, the table below will track monthly total overtime by reason.
    If you look at D4 on this example, it shows one position doing overtime for four different reasons. The table at the bottom has tracked frequency and type of OT for this position on this day. I need to apply this to all cell columns under "code".
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,216

    Re: Applying multiple formulas to multiple columns

    Please try B54

    =SUMPRODUCT((LEN($A$3:$V$50)-LEN(SUBSTITUTE($A$3:$V$50,Sheet2!$A2,"")))*(LEFT($A$3:$A$50)="#")*($A$3:$V$3="code"))

    Remove space after "A " at Sheet2!$A2

  6. #6
    Registered User
    Join Date
    01-10-2019
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Applying multiple formulas to multiple columns

    Bo_Ry,
    I believe you misunderstood. The columns under "code" is where I want to input letters that signify what category the overtime was for.

  7. #7
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,216

    Re: Applying multiple formulas to multiple columns

    What's wrong with the formula?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-10-2019
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Applying multiple formulas to multiple columns

    Bo_Ry
    That works! Appreciate your help with this.

+ 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