Problems to combine IF, AND, ISBLANK and COUNTIF - pls help!

**Postlki1** · 05-04-2013, 06:42 AM

Hi folks,

I have tried back and forth, but can't get the formula together...

This are some of my trials...

=IF(AND(ISBLANK(L6:L30)),IF(COUNTIF($B$6:$B$30,"Other expenses"),"Specify other expenses!",""))

=IF(COUNTIF($B$6:$B$30,"Other expenses"),IF(ISBLANK(L6:L30),"Specify other expenses!",""),"Specify other expenses!","")

What I want the cell O5 to do is this:
1. Check if any cell in the range B6:B30 contains the text "Other expenses"
2. If so, check cell range L6:L30 and return the text "Specify other expenses" in case the matching cell is empty (matching cell meaning if B8 contains "Other expenses" than L8 should be checked if empty or not)
3. When the matching cell in range L6:L30 has text input (whatever text), cell O5 is to return empty

Basically this is no true or false issue, but a conditional check of user input. Do I make any sense?

Thanks for your help!

Regards,
Kim

**benishiryo** · 05-04-2013, 06:58 AM

hi Kim, something like this?
=IF(COUNTIFS(B6:B30,"*Other expenses*",L6:L30,""),"Specify other expenses","")

you mentioned "contains Other expenses", so i included the asterisk. if you want it to be exactly "Other expenses" & not include others like "all other expenses", then:
=IF(COUNTIFS(B6:B30,"Other expenses",L6:L30,""),"Specify other expenses","")