Create a Count of how many Not-Completed items to use in Dashboard

VinceA
VinceA ✭✭✭
edited 11/21/23 in Formulas and Functions

Seems like this should be easy but I'm hitting snags. Please see both screenshots below.

I just want to create a Count of how many Not-Completed items are on my sheet to bring into a Metric sheet to then display as a Widget # on a new Dashboard.

Funny thing is, I've built out some pretty nice Dashboards but I'm hitting a snag here.

Below is what I was using as a Formula on a Metric sheet I was creating to show what is Not-Completed, based off the another column being populated from the Worksheet I am using to create the Metric Sheet.

=COUNTIFS({Summary of Not-Completed}, "", {Carton Evaluation Log Sheet}, <>"")

Thanks much Community.

Vince

Best Answer

  • VinceA
    VinceA ✭✭✭
    Answer ✓

    Thank you much Jason.

    Yes I do have a little know-how on Sheets and dashboards and formulas as seen in some of my other recent posts, but sometimes the simplest of things will leave me stumped lol.

    Your formula helped but I tweaked it because it was taking into consideration all of the empty fields from empty rows on the sheet and giving me more then what was actually there for incomplete projects, so I changed the formula to trigger if another field is populated to then give me the results for what I was aiming at.

    Thanks Jason and Happy Thanksgiving to you.

    Kind regards,

    Vince

Answers

  • Jason P
    Jason P ✭✭✭✭✭

    Hi Vince,

    2 ways .

    Formula. =Countifs([Checkbox Column Name]:[Checkbox Column Name], =0) Change to 1 if wanting to count ticks.

    Report. Create a report from the sheet and using filters, summary fields & hding columns would achieve the result.

    Hope this helps

    Cheers.

  • VinceA
    VinceA ✭✭✭
    edited 11/21/23

    Thanks for the reply Jason.

    Yes the Report feature is great but you can't bring that in necessarily as a Widget to display a single # of however many projects in this case are displaying as not Check-Marked as Complete.

    The formula gives me #UNPARSEABLE

  • Jason P
    Jason P ✭✭✭✭✭

    Hey Vince,

    Looks like a cross sheet formula? try =COUNTIFS({Name of sheet 1}, =0).

    When you type in the cell =countifs( Smartsheet will open a info box, and highlight (range1, and a link Reference Another Sheet. Click this, find your sheet and select your Completed column, this is your range 1. In my case the formula looks like =COUNTIFS({Construction Schedule Range 1}, =0)

    Hope this help, not sure how well versed you are in Sheets. Any more than this and I could be getting out of my knowledge base.

    Cheers.

  • VinceA
    VinceA ✭✭✭
    Answer ✓

    Thank you much Jason.

    Yes I do have a little know-how on Sheets and dashboards and formulas as seen in some of my other recent posts, but sometimes the simplest of things will leave me stumped lol.

    Your formula helped but I tweaked it because it was taking into consideration all of the empty fields from empty rows on the sheet and giving me more then what was actually there for incomplete projects, so I changed the formula to trigger if another field is populated to then give me the results for what I was aiming at.

    Thanks Jason and Happy Thanksgiving to you.

    Kind regards,

    Vince

  • Jason P
    Jason P ✭✭✭✭✭

    👍️

    Cheers.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!