SOLVED

# Problems with finding the right IF formula

Copper Contributor

# Problems with finding the right IF formula

Hi, I am trying to get a formula that can give 3 results/returns (Resolved, Pending and blank). There is a Status column where the answer returns and 2 other columns where I compare data to see if they are filled with various names. If the 2 columns contain anything I want it to state "Resolved". If only 1 out of the 2 columns is filled I want it to state "Pending". If both columns are empty the cell should remain blank.

Below is the current formula and image of the table I am using but it either gives me resolved if the names appear in either column or both and if both cells are empty it remains blank my problem is to get the "Pending" return if only 1 of the 2 cells are filled.

=IF(OR(COUNTIF(H2:I2,"*"&L4:L7&"*")),"RESOLVED","")

4 Replies
best response confirmed by RebekahR1415 (Copper Contributor)
Solution

# Re: Problems with finding the right IF formula

@RebekahR1415 One possibility could be:

``=IF(AND(LEN(H2:I2)), "Resolved", IF(OR(LEN(H2:I2)), "Pending", ""))``

# Re: Problems with finding the right IF formula

Hi, it's giving 0 as the response. Not sure what I did wrong.

# Re: Problems with finding the right IF formula

@RebekahR1415 Copy the formula I used, then paste it into the formula bar with cell G2 selected.

If you're typing it out manually, make sure all of the brackets and commas are in the correct spot.

# Re: Problems with finding the right IF formula

It worked! Thank you
1 best response

Accepted Solutions
best response confirmed by RebekahR1415 (Copper Contributor)
Solution

# Re: Problems with finding the right IF formula

@RebekahR1415 One possibility could be:

``=IF(AND(LEN(H2:I2)), "Resolved", IF(OR(LEN(H2:I2)), "Pending", ""))``