+ Reply to Thread
Results 1 to 8 of 8

Multiple If AND criteria formula help

  1. #1
    Registered User
    Join Date
    08-19-2015
    Location
    Portland, Oregon
    MS-Off Ver
    Office 365 ProPlus
    Posts
    20

    Multiple If AND criteria formula help

    Hi,

    I need a formula that will:
    1) perform one calculation if a nearby cell (E31) is blank (if blank, then cost * markup)
    2) Perform another calculation if it is populated and a second cell says "T&M" (if populated and "T&M, then takes the labor sell rate ($65) and multiplies by the quantity entered (E31)
    3) Perform another calculation if it is populated and a second cell says "Estimate" (if populated and "Estimate" then takes labor cost rate and multiplies it by the MU (1.7) (F6) and multiplies that by the quantity entered (E31).
    4) Remain blank if quantity is 0 and nothing is entered into the cost cell (I31)

    I loaded the "experimental" workbook below."v3.3"

    This calculation is more complicated that I can solve on my own, so I'm asking for help.

    Thanks,

    -Rod
    Attached Files Attached Files

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Multiple If AND criteria formula help

    If I understand your specs correctly this formula should go in cell J31
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I assumed that "cost" and "markup" in rule 1 are cells I31 and G31 respectively.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Registered User
    Join Date
    08-19-2015
    Location
    Portland, Oregon
    MS-Off Ver
    Office 365 ProPlus
    Posts
    20

    Re: Multiple If AND criteria formula help

    Thanks for taking time Tsjallie.
    This is super close, but is not capturing the cost when I enter it into column I.
    It needs to be able to either capture the HOURS COST in the cost field (based on the hours I enter in column E), or the MATERIALS cost based on the dollars I manually enter it into column I.
    I want to be able to just grab the anchor and drag down so the entire column (J) can accomplish those tasks. Rather than having to enter one calculation in J in the hours rows and another in the materials rows.

    Does that make sense?

  4. #4
    Registered User
    Join Date
    08-19-2015
    Location
    Portland, Oregon
    MS-Off Ver
    Office 365 ProPlus
    Posts
    20

    Re: Multiple If AND criteria formula help

    Also, Column G does nothing functional. I should have deleted it before posting.
    The real MU shows up in F6.

  5. #5
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Multiple If AND criteria formula help

    Rechecked the formula against the rules you mention. Formula starts with rule #1 and then #2, #3 and finally #4. As far as I can see the formula does what you require it to do, but as it is a nested formula the results depend on quite some conditions. So that may be confusing. But then again may be I didn't use all the right cells.

    Can you be more specific on what cell(s) in column I hold the cost. And what are the "hours rows" and "materials rows".

  6. #6
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Multiple If AND criteria formula help

    If column G is obsolete then $G31 should be replaced by $F$6
    This would result in
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-19-2015
    Location
    Portland, Oregon
    MS-Off Ver
    Office 365 ProPlus
    Posts
    20

    Re: Multiple If AND criteria formula help

    Awesome!

    It works!

    Thank you, thank you, thank you, thank you.


  8. #8
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Multiple If AND criteria formula help

    You're welcome. Thx for the rep.

+ 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: 4
    Last Post: 04-14-2017, 05:14 PM
  2. [SOLVED] Formula to count occurrences, multiple ranges, multiple criteria, with wildcard
    By TMMc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-23-2017, 03:27 PM
  3. [SOLVED] Need Excel formula to use INDEX and MATCH with multiple criteria's over multiple ranges.?
    By mchilapur in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-22-2017, 08:56 AM
  4. Formula to Sum 3 Column Values based on Multiple Criteria for Multiple Rows
    By RMerckling in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-19-2016, 07:46 PM
  5. Formula for summing on multiple row criteria and a dynamic column criteria
    By ianswilson815 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-25-2016, 01:58 PM
  6. Replies: 12
    Last Post: 02-10-2014, 11:59 AM
  7. Replies: 1
    Last Post: 05-16-2011, 05:00 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