# Count based on two Columns

Options
✭✭✭✭
edited 02/19/21

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 02/25/21 Answer ✓
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

Thank you so very much!!!!

• ✭✭✭✭✭✭
Options

:) 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!

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 02/25/21 Answer ✓
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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