COUNTIFS + FIND function help

Sean_H

Hello!

I am trying to count the number of rows with a non-blank cell in one column and a blank cell in another column. The non-blank cell contains a contract number that looks like 18-A-001-SH and everything but the leading 18 changes from one row to the next.

I started with this formula but it is returning a value of zero

=COUNTIFS({FY 18 Contract Number Range 3}, FIND("18", {FY 18 Contract Number Range 3}) = 1, {FY 18 Total Cost Range 1}, "")

Thanks in advance for any help,

Sean

Nic Larsen

Give this a try:

But is the 2nd range correct? It appears you are searching the same range twice. 

=COUNTIFS({FY 18 Contract Number Range 3}, CONTAINS(18, @cell), {FY 18 Contract Number Range 3}), ISBLANK(@cell), {FY 18 Total Cost Range 1}, "")

 

Sean_H

Perhaps I was searching twice because this is what worked

=COUNTIFS({FY 18 Contract Number Range 3}, CONTAINS(18, @cell), {FY 18 Total Cost Range 1}, "")

Thank you very much for your help!