+ Reply to Thread
Results 1 to 10 of 10

If formula Help?!

  1. #1
    Registered User
    Join Date
    09-10-2020
    Location
    Jamesburg, NJ
    MS-Off Ver
    2016
    Posts
    6

    If formula Help?!

    Hello Everyone! Long time reader, first time poster. I am having trouble with an IF formula. Can someone help me write this formula correctly:

    IF formula help.jpg

    I want the total from column L (or M depending on the the project type, so L for site but M for others) to show up in either columns D, E, F, G, or H depending on on column C. If column C says A I want the total from L to show up in column D. If C says B, total from L should be in column E and so on. The idea would be to move that row to a different priority column but changing column c's input.

    I tried a few If formulas but I cannot get this to work.

    Any advice or direction?

    Thanks to all!

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: If formula Help?!

    so, assuming you have A, B, C etc down column C, if you are willing to put A through H across row 1 from cell D1 through K1, you can use this single if statement then dragged across and down...
    =IF($C2=D$1,$L2,"")
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    09-10-2020
    Location
    Jamesburg, NJ
    MS-Off Ver
    2016
    Posts
    6

    Re: If formula Help?!

    when you say put A-H across row 1 from D1-K1 do you mean that formula in those blank cells?

    Dan

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: If formula Help?!

    If column C says A I want the total from L to show up in column D. If C says B, total from L should be in column E and so on. The idea would be to move that row to a different priority column but changing column c's input.
    I was basing it on this part.
    My assumption is in the attached workbook.
    I don't know what you want done with column M.
    maybe you can put together a simple limited example workbook to make it more clear including what you have AND what you want under which conditions, follow the instructions in the yellow banner at the top of the post to upload a sample.
    Attached Files Attached Files

  5. #5
    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: If formula Help?!

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    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

  6. #6
    Registered User
    Join Date
    09-10-2020
    Location
    Jamesburg, NJ
    MS-Off Ver
    2016
    Posts
    6

    Re: If formula Help?!

    Thanks! I made the attached sample workbook. For the rows under site work I wanted the total from colum L (calculated by the quantity and cost inputs) to be in D-H depending on C.
    I wanted the totals from column M instead of L for the rows under exterior envelope.

    Hope this helps.
    Attached Files Attached Files

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: If formula Help?!

    I'm still not sure I'm understanding what you are wanting but this is what I came up with...
    for D5 through D12 =IF($C5="a",$L5,0), for E5 through E12 =IF($C5="b",$L5,0), keep repeating as you move toward the right.
    for exterior work then =IF($C14="a",$M14,0) in D14, =IF($C14="b",$M14,0) in E14 and keep changing for F, G and H then drag down.

  8. #8
    Registered User
    Join Date
    09-10-2020
    Location
    Jamesburg, NJ
    MS-Off Ver
    2016
    Posts
    6

    Re: If formula Help?!

    That works, but it is rounding, is there a way to get exact value, or is that just a cell formatting issue? This works exactly as I need though!

    Thank you so much for your help!

  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: If formula Help?!

    If you want to round to get an exact figure, use the ROUND (it will decide which way to round), ROUNDDOWN (rounds down) or ROUNDUP

  10. #10
    Registered User
    Join Date
    09-10-2020
    Location
    Jamesburg, NJ
    MS-Off Ver
    2016
    Posts
    6

    Re: If formula Help?!

    Thanks! Got it. Much appreciated!

+ 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. Replies: 3
    Last Post: 03-24-2020, 07:27 AM
  2. Replies: 3
    Last Post: 01-10-2019, 09:34 AM
  3. Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  4. Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  5. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  6. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  7. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Tags for this Thread

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