Count number of times a value appears?

I have a sheet for tracking the amount of defects that occur. Each defect report has a "Machine Name" value which is selected via a dropdown. What I'm trying to achieve is to have a counter, in any form but preferably a widget or a column, that would track the amount of times a report pulls a machine name. I.E. if Machine 1 has 3 reports on my sheet, this value would be recorded. Essentially, I want to track the number of defects that come out of a machine.

I tried making a widget for this, but the data wouldn't work I assume because the values aren't numerals (machines are like A-001 or W-034).

Is this possible? Thanks.

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    Answer ✓

    Glad I could help!

    Is there anything I can add to the formula to only grab this data from a time period, such as 1 month?

    Changing to the "COUNTIFS" function allows you to add additional ranges and criteria. You could add a date field range with criteria ">TODAY(-30)". https://help.smartsheet.com/function/countifs

    Is there a way to hide the rows in which the Machine List populate? I was able to hide the column, however I want to refrain from having a large blank space on the sheet (there are about 150 machines).

    This is why I like using metrics sheets. By moving the machine list and count formulas to the metrics sheet, you maintain the integrity of the main sheet, so that it only contain the data you're tracking and nothing else. Don't be scared off by the added complexity! Smartsheet functions are really good at interactively helping you select ranges from other sheets as you are creating them:

    As you begin your COUNTIFS formula, just click on Reference Another Sheet, select your main sheet, and select the column you're going to look in. Smartsheet then creates a reference to that main sheet range in your metrics sheet. (As a best practice, I rename the range reference to something meaningful so that I or someone else can figure out what data we're pulling from months or years later! For example I would rename "Main Sheet Range 1" to "Main Sheet Part Number".)


    Alternatively, you can create sheet summary fields that include your formulas, but with 150 unique machines, this would mean 150 summary fields and I'm not even sure you can add that many!

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

Answers

  • *Essentially, I want to track the number of times a machine has a defect

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭

    Create a column that lists each Machine Name and call it "Machine List" or something like that. Try this in an empty column in the same sheet, on each row that corresponds to the entries in the Machine List:

    =COUNTIF([Machine Name]:[Machine Name], [Machine List]@row)

    This will look through the Machine Name column and count the number of times it finds that row's Machine List entry in the Machine Name column.

    You could also do this in metrics sheet, running the COUNTIF against the range in the main sheet.


    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Kyle Cannella

    I hope you're well and safe!

    The simplest method would probably be to use a report and group it by machine.

    Would that work/help?

    I hope that helps!

    Have a fantastic week & Happy Holidays!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Both are viable options! I decided to go with the first method.


    @JReisman27 Adding upon your method, I created a widget with the Machine List and Report Count cells highlighted, which got me a pie chart that was exactly what I needed.

    2 further questions,

    Is there anything I can add to the formula to only grab this data from a time period, such as 1 month?

    Is there a way to hide the rows in which the Machine List populate? I was able to hide the column, however I want to refrain from having a large blank space on the sheet (there are about 150 machines).

    Thanks for the help!

  • Scratch the hiding rows question, achieved with filtering.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    Answer ✓

    Glad I could help!

    Is there anything I can add to the formula to only grab this data from a time period, such as 1 month?

    Changing to the "COUNTIFS" function allows you to add additional ranges and criteria. You could add a date field range with criteria ">TODAY(-30)". https://help.smartsheet.com/function/countifs

    Is there a way to hide the rows in which the Machine List populate? I was able to hide the column, however I want to refrain from having a large blank space on the sheet (there are about 150 machines).

    This is why I like using metrics sheets. By moving the machine list and count formulas to the metrics sheet, you maintain the integrity of the main sheet, so that it only contain the data you're tracking and nothing else. Don't be scared off by the added complexity! Smartsheet functions are really good at interactively helping you select ranges from other sheets as you are creating them:

    As you begin your COUNTIFS formula, just click on Reference Another Sheet, select your main sheet, and select the column you're going to look in. Smartsheet then creates a reference to that main sheet range in your metrics sheet. (As a best practice, I rename the range reference to something meaningful so that I or someone else can figure out what data we're pulling from months or years later! For example I would rename "Main Sheet Range 1" to "Main Sheet Part Number".)


    Alternatively, you can create sheet summary fields that include your formulas, but with 150 unique machines, this would mean 150 summary fields and I'm not even sure you can add that many!

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Kyle Cannella

    Excellent!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • The metric sheet was exactly what I needed. Everything works perfectly now, thanks guys!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Kyle Cannella

    Glad you got it working as you needed it!

    I'm always happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected]rkbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.