Count number of times a value appears?

Kyle Cannella

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.

Jeff Reisman

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!

Kyle Cannella

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

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.

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!

Kyle Cannella

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!

Kyle Cannella

Scratch the hiding rows question, achieved with filtering.

Jeff Reisman

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!

Andrée Starå

@Kyle Cannella

Excellent!

Happy to help!

Kyle Cannella

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

Andrée Starå

@Kyle Cannella

Glad you got it working as you needed it!

I'm always happy to help!