Count based on two Columns

Nancy Heater

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!

Heather Duff

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

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

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

Heather Duff

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

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

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

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

Thank you so very much!!!!

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

@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

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

@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

@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.