Automating an IF function formula.

[davidharper2005]

I have the following formula =IF(AND(G3=G2,BO3=BO2),"",BP3) where the G column is the client name and the BO column is the sub-service name and BP is the service name. As this formula is reliant on the client names and services being sorted alphabetically to work correctly, is there a simpler formula that doesn't rely looking on cell above but throughout the whole column to see if there any other duplicate values so that if I sort the spreadsheet it will still return the same values.

[cbatrody]

How about this?

=IF(COUNTIFS(G:G,G3,BO:BO,BO3)>1,"",BP3)

[davidharper2005]

Thanks cbatrody but it doesn't work. It only picked up some of the values but not all of them. Any other suggestions?

[cbatrody]

Can you post a sample template of your workbook please?

[davidharper2005]

Here you go. I'm looking to put the new formula in column BQ.

Excel Forum Posting (3).xlsx

[cbatrody]

Hi,

Try the following formula in BQ2:

=IF(COUNTIFS(BP$2:BP2,BP2,G$2:G2,G2)>1,"",BP2)