Create a chart that shows a count of names, MoM

davidsmi

Hello,

I have set up a sheet using calendar view that shows the name of different speakers at events each month (eg: Bob Smith is speaking on Dec 3, 6, 7,11 and on January 6 and on Feb 2, 4. Mary Jones is speaking on December 4, 5, 6 and Jan. 7,8)

I would now like to chart, on a dashboard, how many times each person speaks each month (eg: Bob Smith spoke 4 times in December, 1 time in January, and twice in Feb. Mary Jones spoke 3 times in December and 2 times in January).

I created a helper column to show the month/year the person speaks but can only figure out how to track the total number of speakers each month. Is there a way for me to break this down by individual speaker name?

Appreciate any guidance (fairly new Smartsheet user here)

Genevieve P.

Hey @davidsmi

You can use a COUNTIFS (plural) to look for multiple criteria - Month and Person. What I would personally do here is have a separate "Metric" sheet that uses a cross-sheet formula to create this calculation.

Down one column you would list each person's name, then in each column you could have the Month, like so:

The top row lists out the Month as a number. This eliminates then need for helper columns in your source sheet for the Month and Year.

Then you can use a COUNTIFS to count how many rows have the Speaker Name from the Speaker column, the Month Number from the top row, and the specific Year you're looking for:

The first reference, {Speaker} looks at the column with the names:

The second reference, {Date}, looks at your speaking date column:

=COUNTIFS({Speaker}, $Speaker@row, {Date}, IFERROR(YEAR(@cell), 0) = 2023, {Date}, IFERROR(MONTH(@cell), 0) = [Sept 23]$1)

• The IFERRORs around the YEAR and MONTH functions make sure the formula skip cells that are blank.
• The $ sign in front of $Speaker makes sure the formula always looks at that column on the left
• The $ sign in front of the 1 makes sure the formula always looks at the top row for the Month number

This means you can drag-fill the formula down the column and across rows for it to automatically adjust for each individual speaker. You'll just need to manually change the 2023 to 2024 when you get into next year.

Here are some articles for reference:

• Create and edit formulas in Smartsheet
• Create cross sheet references to work with data in another sheet
• COUNTIFS Function

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

davidsmi

Hi Genevive,

This makes sense but I can't get it to work. :-(

This is my formula...

=COUNTIFS({Speaker Schedule Range 3}, $Speaker Schedule Range 3@row, {2023 Speaker Schedule Range 2}, IFERROR(YEAR(@cell), 0) = 2023, {2023 Speaker Schedule Range 2}, IFERROR(MONTH(@cell),0) = [Nov 23]$1)

{Speaker Schedule Range 3} is referencing the speaker column on my main sheet.

{2023 Speaker Schedule Range 2} is referencing the date column on my main sheet.

I have figured out a work around by creating a new report for each month and then grouping by the speaker name and month/date of the event, but this means I have to remember to create a new report each month.

Genevieve P.

Hi @davidsmi

What are your column names in the sheet that has the formula? It looks like one of your references doesn't exist, so you're likely getting an Unparseable error.

I've bolded the reference below:

=COUNTIFS({Speaker Schedule Range 3}, $Speaker Schedule Range 3@row, {2023 Speaker Schedule Range 2}, IFERROR(YEAR(@cell), 0) = 2023, {2023 Speaker Schedule Range 2}, IFERROR(MONTH(@cell),0) = [Nov 23]$1)

Notice that in my screen capture the reference is Blue to indicate it's looking at the cell in the Speaker column on the left, highlighted in blue. You'll want this reference to be the title of the column in your current sheet that houses the speaker names.

Make sure you surround it in square brackets, too:

=COUNTIFS({Speaker Schedule Range 3}, $[First Column]@row, {2023 Speaker Schedule Range 2}, IFERROR(YEAR(@cell), 0) = 2023, {2023 Speaker Schedule Range 2}, IFERROR(MONTH(@cell),0) = [Nov 23]$1)