Help with IF function

[southcali12]

Hello,

I am trying to create an IF statement for column H to determine an available time for each client.

In column H, I am only looking at the preferred columns (B,D,F). If there are more than three duplicate times per column, I want column H to return with "Look at Preferred Options" so that way I know I have to look at their preferred availability. However, if there are not three or more duplicate times per column, then I want it to return with the preferred value for each client.

The formula I have right now in column H is: =IF(COUNTIF(B:B,B3)=3,"Look at Preferred Options",IF(COUNTIF(D:D,D3)=3,"Look at Preferred Options",IF(COUNTIF(F:F,F3)=3,"Look at Preferred Options",(B3,D3,F3))))

However, I understand that what I have underlined and bolded above for when the statement is false is not correct, but am lost at how to fix it so that it will look through the preferred times and return the preferred value. I hope that made sense!

Reference of what my sample looks like:
[TABLE="width: 812"]
<tbody>[TR]
[TD]Client
[/TD]
[TD]Preferred
[/TD]
[TD]Alternate
[/TD]
[TD]Preferred
[/TD]
[TD]Alternate
[/TD]
[TD]Preferred
[/TD]
[TD]Alternate
[/TD]
[TD]Selected Time
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]10/26/2015
[/TD]
[TD]10/26/2015
[/TD]
[TD]10/27/2015
[/TD]
[TD]10/27/2015
[/TD]
[TD]10/28/2015
[/TD]
[TD]10/28/2015
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]4:00 - 5:00 PM
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]3:00 - 4:00 PM
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Look at Preferred Options
[/TD]
[/TR]
[TR]
[TD]Sally
[/TD]
[TD]4:00 - 5:00 PM
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2:00 - 3:00 PM
[/TD]
[TD]Look at Preferred Options
[/TD]
[/TR]
[TR]
[TD]Maggie
[/TD]
[TD]
[/TD]
[TD]12:00 - 1:00 PM
[/TD]
[TD]6:00 - 7:00 PM
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]#VALUE!
[/TD]
[/TR]
[TR]
[TD]Joe
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]10:00 - 11:00 AM
[/TD]
[TD]8:00 AM - 9:00 AM
[/TD]
[TD]
[/TD]
[TD]#VALUE!
[/TD]
[/TR]
[TR]
[TD]Lily
[/TD]
[TD]4:00 - 5:00 PM
[/TD]
[TD]1:00 - 2:00 PM
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Look at Preferred Options
[/TD]
[/TR]
[TR]
[TD]Harry
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1:00 - 2:00 PM
[/TD]
[TD]4:00 - 5:00 PM
[/TD]
[TD]
[/TD]
[TD]#VALUE!
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

This should do, I added greater than along with equal to 3, cause your client list is most likely going to be longer...:

=IF(OR(COUNTIF(B:B,B3)>=3,COUNTIF(D:D,D3)>=3,COUNTIF(F:F,F3)>=3),"Look at Preferred Options",IF(B3<>"",B3,IF(D3<>"",D3,F3)))

Let me know if this works for you.

 

[southcali12]

Hi jtakw,

It works perfectly. Even better since you added the greater than 3. Thank you so much!

However, do you now how I could adjust the formula if my data is all pulled from VLOOKUPs? So all the data in the table is all pulled from VLOOKUPs, and all the cells that appear empty, technically, have formulas in them. And correct me if I'm wrong but the IF formula won't work then right?

 

[jtakw]

Hi,

I'm not sure if I understand exactly what you mean..but, if your data as shown in your original post comes from VLOOKUPs, the IF formula should still work.

 

[southcali12]

I think the blank cells are being recognized as having data in them because in the formula bar there is the VLOOKUP formula. So the IF function will return "Look at Preferred Options" even if there isn't 3 or more of the same times listed in each column.
It could be something else that I'm doing, but when I copy and paste values over, the IF function will return with "Look at Preferred Options" regardless of the amount of time slots. However, if I manually type in each data, then the IF function works perfectly. :D

 

[jtakw]

Hi,

=IF(OR(COUNTIF(B:B,B3)>=3,COUNTIF(D:D,D3)>=3,COUNTIF(F:F,F3)>=3),"Look at Preferred Options",IF(B3<>"",B3,IF(D3<>"",D3,F3)))

Let me know if this works for you.

No, because it's looking in column B for a match of B3 ONLY, so whatever else are in the other cells won't match; however, you may want to limit the array, instead of B:B to B2:Bwhatever_row, D:D to D:Dwhatever_row, etc....

An actual sample of your sheet would help...

 

[southcali12]

Hi jtakw,

Thanks for all your help; the formula you suggested does work perfectly.

I realized it was the blank cells in my sheet that was messing up the formula. Not entirely sure why, but apparently they had some form of code in them: Excel telling me my blank cells aren't blank - Stack Overflow

But problem solved now! Thanks again.

 

[jtakw]

Hi,

I think I know what is causing your problem, your blank cells probably is not blank, but have a space in them (looks blank but it's not), so the formula will count the spaces in the column, thereby giving you the wrong (actually correct) result of "Look at Preferred Options".
Since the data table is a result of your VLOOKUP, the space is most likely from the original data set you're looking up.
Check the cell formulas in the original data set that looks blank for " ", and replace them with "". (" " is a space, whereas "" is blank), unless intentional; otherwise using " " in formulas instead of "" will cause all kinds of problems down the road.

As an example:
=IF(A1=B1,C1," ")
should be:
=IF(A1=B1,C1,"")

 

[jtakw]

In case your VLOOKUP formula is inside an IF formula, please check it for the same problem described above.

 

[southcali12]

Hi jtakw,

I think that may have been the issue. Some of the cells did look like there was a space. Thanks!