Help! Formula for results across multiple columns
Hi!
I am new to Smartsheet, and I need assistance with a formula that includes data from multiple columns. My desired result is to capture a Month Over Month trend.
In the screen shot, I have the two columns I am working with.
I need to be able to display how many 5 ratings for January, how many 5 ratings from February, etc... I have tried multiple formulas, unfortunately, I keep getting parsing errors.
Any assistance would be greatly appreciated! TIA!
Best Answer

Depending on how you have your source sheet setup, will help determine what graph type you can use on your dashboard. I created a simple bar chart that may be what you're looking for.
For this chart the legend is placed below with the column header as the name of the key. I also deactivated the "Switch rows & Columns" setting, and activated the "Always show value label" options
Here is what the source sheet looks like:
The Rating and Date columns circled in blue show your source data.
The Month and # of 5 Stars columns are for your dashboard graph and the formula.
In the # of 5 Stars column, I have the following: =COUNTIFS(Date:Date, Month@row, Rating:Rating, "5") in each of the cells next to the month. That way I can easily reference the month in the formula.
Count if the Date in the Date column matches the month in the row AND if the rating in the rating column is 5.
Date:Date means the formula checks the entire column.
Month@row tells the formula to look at the month column for the row the formula is in. So if you have the formula in the # of 5 Stars column next to January, than the Month@row will look for January in the Date column. Setting up the formula as an @row also allows you to just put the formula in for january and drag it down for the rest of the months instead of typing =COUNTIFS(Date:DATE, "January" .... and having to update the formula for each month.
Answers

You might be able to get what you're looking for in Work Insights (a fairly new feature in the right rail)
https://help.smartsheet.com/learningtrack/advanced/workinsights
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY business optimization product manager

Unfortunately, I need to create a dashboard with the information.

Depending on how you have your source sheet setup, will help determine what graph type you can use on your dashboard. I created a simple bar chart that may be what you're looking for.
For this chart the legend is placed below with the column header as the name of the key. I also deactivated the "Switch rows & Columns" setting, and activated the "Always show value label" options
Here is what the source sheet looks like:
The Rating and Date columns circled in blue show your source data.
The Month and # of 5 Stars columns are for your dashboard graph and the formula.
In the # of 5 Stars column, I have the following: =COUNTIFS(Date:Date, Month@row, Rating:Rating, "5") in each of the cells next to the month. That way I can easily reference the month in the formula.
Count if the Date in the Date column matches the month in the row AND if the rating in the rating column is 5.
Date:Date means the formula checks the entire column.
Month@row tells the formula to look at the month column for the row the formula is in. So if you have the formula in the # of 5 Stars column next to January, than the Month@row will look for January in the Date column. Setting up the formula as an @row also allows you to just put the formula in for january and drag it down for the rest of the months instead of typing =COUNTIFS(Date:DATE, "January" .... and having to update the formula for each month.

Thank you! This is AMAZING! And worked perfectly!

You're most welcome @jal1172 . I'm glad I was able to help
Help Article Resources
Categories
Check out the Formula Handbook template!