percentage

lizdesi

I'm trying to have my smartsheet calculate a percentage, Ex one column is "Base Size" and we are trying to allocate 3% of device based off the "Base Size"

Kelly Moore

Hey @lizdesi

When multiplying to obtain a percent, one needs to multiply by the decimal value (3%=0.03).

=[Base Size]@row*0.03

Will this work for you? Please don't hesitate to ask more questions.

Kelly

lizdesi

Thank you so much @Kelly Moore

lizdesi

@Kelly Moore Maybe you can please help me with another road block I'm facing for the dashboard I'm creating. Looking to add the Symbols ( Yes or No ) within two separate columns. Do not want to add them all together rather add all the Yes's in that one column and all the No's too. I'm having trouble with the formal I'm using, keeps receiving errors.

Any help will be greatly appreciated.

Kelly Moore

Hey @lizdesi

Sure, happy to help. If I understand you correctly, you are going to add two decision-symbol columns, and you want one of them to be for Yes and one to be for No? We can do that, if you wish. As an alternative, you realize you could build a filter that could easily sort on Yes or No's? Also, for clarification, all of the data lives in the sheet where your formula will be placed- that is, we will not be gathering data from a different sheet?(smartsheet calls a different sheet a cross referenced sheet)

If you share a screenshot of your column names I can help you build the specific formula you are looking for.

lizdesi

Not quite. My apologies if I did not explain well the first time. I've attached a photo for reference. As you can see, two separate columns use the Yes or No symbols. For each, I'm looking to find the total of Yes or No's. I've already gone ahead and created additional columns for total devices picked up and total devices returned.

Kelly Moore

Hey @lizdesi

So do you now have what you need? As Brent mentioned in the other post, instead of separate columns, you could use the Summary Fields on this sheet (right hand menu ribbon) and create a field for Total Devices Picked Up = Yes and Total Devices Dropped Off = Yes, and these fields would use the formulas he gave you. You would also need fields for your Totals so you can get a percentage. Summary fields can be brought to Dashboards.

As I look at your first 3 rows of your screenshot, For the number of Yes's, are you expecting the count to be 5, or the count to be 2 (only 2 out of the 3 rows have Yes's in both columns)?

Are we collecting the information in this sheet, or are you bringing the information over to a different sheet - like a metrics sheet for a dashboard?

Kelly

lizdesi

When I create a field with a sheet summary and attempt to input the formula from the other post it receives an error.

Only the two columns will have a number of Yes's and No's, but we have a total of 6 sheets that have those columns, that information will be pulled for a sheet summary or a metrics dashboard in the future.

Yes, we are collecting the information in this sheet and creating metrics for a dashboard. The data from this sheet ( and others that look like this but record data from a separate location) will be pulled for another sheet but at a later time.

I'm still very new to smart sheets and sheet summaries. This is my first project as our company transitions

Kelly Moore

Hey @lizdesi

No worries, we'll work through your needs step by step. I'll may ask a number of questions, I'll apologize up front. The questions are only to make sure I am understanding what you need. In my previous post, did you answer my question if you were expecting an answer of 2 Yeses from the first three rows of your screenshot, or 5? The formula is different between the two answers.

Can you give me a screenshot of which of the formula(s) you are using that is producing errors- and what error? Also, with 6 sheets we might do something differently than sheet summaries, depending on what data you need for the dashboard. Do the 6 sheets have identical columns- ie, essentially it is the same sheet but 6 times?

What does your metric sheet look like? Is it possible to get a screenshot? As an fyi, screenshots ALWAYS help the community better understand what a person needs and what issues they are having.

The screenshot of the metrics sheet will probably answer this question, but are you tracking/recording the Totals month by month (or some duration), or just posting a running total? At the moment the formulas from the other post do not filter on any date range.

lizdesi

There are only two rows where you can choose either Yes or No to confirm whether the device was picked up and then dropped off. These columns are not filled out simultaneously, the pickup column is typically filled out first. We are wanting to post a running total, so maybe having the formula running every week so we can have a week-to-week overview of how many devices are being picked up and dropped off.

Yes, each sheet that I have has identical columns. Each sheet is for a different location to input data based on inventory for our end users.

Attaching a photo of the rows where you can select yes or no, the middle columns are blank because I had to cover the sensitive information. I've created two columns at the end of the sheet where I've indicated total devices picked up and total devices returned.

I do not have a photo of the dashboard I've created just yet. Attaching a photo of my master sheet, which each row will most likely be broken down into individual sheets so that those bases can have their respective employee's fill them out.

Kelly Moore

Hey

We can build all the formulas you need - either on the individual sheets or using cross sheet formulas. However, with 6 identical sheets you may be able to get what you need with a report. Here is an example of what reports would look on the dashboard. To make the reports I added 4 columns to my sheet: Date Picked Up column, Month Picked column and Date Returned, Month Returned columns. The date columns allows Date Recorded automation to capture the date when the respective field is marked "Yes". The Month columns allow a report to be grouped by Month. In terms of ease of solution, this is the simplest way to display the results of 6 sheets. Listed below are two reports - one for each column, filtered where that column = Yes. Thus the count, by location, are your Yes's.

Would this work for you? If not, we can build the formulas.

Kelly

lizdesi

Mary thank you so much for your help. I've been working through your feedback and created a formula for counting how many Yes's and how many No's there are.

However, now I'm trying to count all the check or no check fields to get totals. Attached is a screenshot of the formula I'm attempting to use (similar to the one for yes or no). I'm getting stuck on where to provide the value for the checked and unchecked ( Either stating it as 1 or 0 or True (checked) and False(unchecked)

Andrée Starå

Hi @lizdesi

I hope you're well and safe!

Try something like this.

=COUNTIF([Removed From DEP?]:[Removed From DEP?], 1)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

lizdesi

@Andrée Starå Thank you that working. However, when creating another column for pending devices to count the unchecked value is not correct. When I use the formula =COUNTIF([Removed From DEP?]:[Removed From DEP?], 0)

It gives me a value that does not total the number of rows actually in the chest. Screenshots for reference. The total of counts for 0 / False were 45 but there are only about 20 rows being used in the sheet.

Kelly Moore

Hey

Building on Andreé's formula - the formula is picking up the empty rows in the sheet. We need to choose a column that will always have data in it- typically I choose my primary column but it doesn't matter - whatever other column will always have data in it. In the formula below, I will call it the Primary column (replace Primary with whatever your's is actually named, The formula calls out for rows that are not blank). Also, because we are using multiple criteria you cannot use a COUNTIF but must use a COUNTIFS. Know that COUNTIFS can be used for any number of criteria, including only one. COUNTIF can ONLY be used for one criteria.

=COUNTIFS(Primary:Primary, <>"", [Removed from DEP?]:[Removed from DEP?],0)

Does this give you the expected result?

Kelly