+ Reply to Thread
Results 1 to 3 of 3

Formula to look at a different cell for criteria if a cell is blank

  1. #1
    Registered User
    Join Date
    08-13-2019
    Location
    Virginia Beach, VA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    4

    Formula to look at a different cell for criteria if a cell is blank

    Hi Everyone!

    I am trying to automate a template so that when a company and labor category are selected, the rate will populate limiting a person from entering the wrong rate. Since there are 2 different criteria, I decided to use a SUMIFS formula =SUMIFS('Contract Rates'!D3:D119,'Contract Rates'!A3:A119,A10,'Contract Rates'!B3:B119,B10) which works but my problem is with blank cells. The format we send to our client does not have the company name listed next to every labor category and I would really like to keep it that way. Is there a way to tell cell D11 if cell A11 is blank, look at cell A10 for the company's name? I tried nesting an IF formula with it but it just put what was in cell A10 instead of looking for the rate so I removed it.

    Attached is a workbook example. Any insight would be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,264

    Re: Formula to look at a different cell for criteria if a cell is blank

    In D10:
    Please Login or Register  to view this content.
    With LOOKUP(2,1/($A$7:A10<>""),$A$7:A10) instead of A10, to refer to last cell that is not blank.

  3. #3
    Registered User
    Join Date
    08-13-2019
    Location
    Virginia Beach, VA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    4

    Re: Formula to look at a different cell for criteria if a cell is blank

    Thank you very much bebo!

+ 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] Need help making COUNTIFS formula display as blank if it's 'criteria cell' is blank
    By 27POP27 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2018, 07:38 AM
  2. Replies: 4
    Last Post: 08-06-2018, 07:54 AM
  3. [SOLVED] Drawing a serious blank - criteria in SUMIF formula to be < a cell value
    By sbeatty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2017, 05:15 PM
  4. Replies: 2
    Last Post: 12-07-2016, 12:39 PM
  5. Replies: 1
    Last Post: 12-06-2016, 06:16 PM
  6. Adjust formula criteria when cell is blank
    By brianmac in forum Excel General
    Replies: 2
    Last Post: 08-20-2010, 01:16 PM
  7. One criteria AND cell is blank in conditional formatting formula?
    By westis in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2008, 06:37 PM

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