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

1. ## 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!

2. ## 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. ## Re: Formula to look at a different cell for criteria if a cell is blank

Thank you very much bebo!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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