COUNTIFS + FIND function help

Sean_H ✭✭✭
edited 12/09/19 in Formulas and Functions


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,




  • Nic Larsen
    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
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!