Count based on two Columns

Nancy Heater
Nancy Heater ✭✭✭✭
edited 02/19/21 in Formulas and Functions

Hello Smarsheet Community!

We need to count specific project types (New, Swap) from the PROJECT column, but only if row has a date in the COMPLETE DATE field.

I've tried a few different combinations of formulas and just haven't been able to get this to work.

Also having an issue trying to count the number of "Incomplete" sites, from the Complete Date field, but its returning #UNPARSABLE.

Link to my test sheet is below.

https://app.smartsheet.com/b/publish?EQBCT=2a8f00cc49eb4021916f06af91e28095

Thank you in advance for your assistance!

Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Nancy,


    Try this formula in your summary for NEW SITES COMPLETE:

    =COUNTIFS(PROJECT:PROJECT, "NEW", [Complete Date]:[Complete Date], NOT(ISBLANK(@cell)))

    And for SWAP SITES COMPLETE:

    =COUNTIFS(PROJECT:PROJECT, "SWAP", [Complete Date]:[Complete Date], NOT(ISBLANK(@cell)))


    Best,

    Heather

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Yep! If you have a column name that has a space in it, you have to put it in square brackets in formulas. So instead of =COUNTIF(Complete Date:Complete Date, CONTAINS("INCOMPLETE", @cell)), you have to use =COUNTIF([Complete Date]:[Complete Date], CONTAINS("INCOMPLETE", @cell)).

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    edited 02/25/21 Answer ✓

    Nancy,

    Try this:

    =COUNTIFS(PROJECT:PROJECT, CONTAINS("Denver 45", @cell), [Complete Date]:[Complete Date], NOT(ISBLANK(@cell)))


    You were really close - you had a comma and space after CONTAINS, and were missing the @cell within the CONTAINS function. I think this should work though.



    Best,

    Heather

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Nancy,


    Try this formula in your summary for NEW SITES COMPLETE:

    =COUNTIFS(PROJECT:PROJECT, "NEW", [Complete Date]:[Complete Date], NOT(ISBLANK(@cell)))

    And for SWAP SITES COMPLETE:

    =COUNTIFS(PROJECT:PROJECT, "SWAP", [Complete Date]:[Complete Date], NOT(ISBLANK(@cell)))


    Best,

    Heather

  • Nancy Heater
    Nancy Heater ✭✭✭✭

    Thank you @Heather D !!!

    Any ideas on the why the count for the number of "Incomplete" sites, from the Complete Date field, is returning #UNPARSABLE?

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Yep! If you have a column name that has a space in it, you have to put it in square brackets in formulas. So instead of =COUNTIF(Complete Date:Complete Date, CONTAINS("INCOMPLETE", @cell)), you have to use =COUNTIF([Complete Date]:[Complete Date], CONTAINS("INCOMPLETE", @cell)).

  • Nancy Heater
    Nancy Heater ✭✭✭✭

    @Heather D - You just blew my mind with that bit of information!!

    Thank you so very much!!!!

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    :) It's the little things in life, isn't it?

    I always try to make my column names without spaces - that way, I don't have to deal with brackets!

  • Nancy Heater
    Nancy Heater ✭✭✭✭

    @Heather D ... found yet another variable we need to have metrics on for the Sheet Summary.

    This would be similar to what we did above, but need to add in a CONTAIN function to only look at Project cells with "Denver 45" and the complete date isn't blank.

    I tried to work in the contains function to what we had done above, but its not working.

    Thanks in advance for the help!

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    edited 02/25/21 Answer ✓

    Nancy,

    Try this:

    =COUNTIFS(PROJECT:PROJECT, CONTAINS("Denver 45", @cell), [Complete Date]:[Complete Date], NOT(ISBLANK(@cell)))


    You were really close - you had a comma and space after CONTAINS, and were missing the @cell within the CONTAINS function. I think this should work though.



    Best,

    Heather

  • Nancy Heater
    Nancy Heater ✭✭✭✭

    @Heather D ... again, thank you so much!

    I'm still learning, but each time I encounter something new, it just helps me continue to improve!

    Thank you again!!!

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    @Nancy Heater You're very welcome! I'm still learning, too - that's part of why I try to answer questions on here! It exercises my brain and makes me look for answers when I can't find them!!


    Have a great day.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!