Help! Formula for results across multiple columns

jal1172

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!

Summer

@jal1172

Depending on how you have your source sheet set-up, 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.

Kelly Drake

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/learning-track/advanced/work-insights

jal1172

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

Summer

@jal1172

Depending on how you have your source sheet set-up, 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.

jal1172

Thank you! This is AMAZING! And worked perfectly!

Summer

You're most welcome @jal1172 . I'm glad I was able to help