How to emulate a pivot table

Pamb10

I have a sheet in excel that contains rows of dates (basically the header of a set of emails including received date). What I need to know is how many rows for each date. In excel I created a pivot table to count on the received date column.

Is there some way to emulate this type of count in SS? I can just as easily put these rows of data in a ss grid. But how can i count how many rows for each date?

The data is extremely simple, a date column. The count needs to be shown date and count and can take be shown in a report or widget on a dashboard.

TIA

Pam Brechlin

Paul Newcome

If you enter the dates you want to count in another sheet, you can use something along the lines of...

=COUNTIFS({Source Sheet Date Column}, @cell = [Date Column]@row)

Pamb10

Hi Paul,

Thanks for the response. Didnt seem to work. Here is what i did....

My input data is simple (sample):

In another sheet, i did the following:

The results came back as 0 always.

----------------------

I did find that I could use a pivot table using the pivot app, and did accomplish the results I wanted. However, Im having no luck in getting the data to display on a Dashboard as a chart.

Ultimately I would like to chart the data per month so I can see the differences per month.

Paul Newcome

That's because your Primary Column is a text/number type (and can't be changed). Put your dates in another column and set that column to a date type.

If you want a per month count, you would use an AND statement to look at month and year.

=COUNTIFS({Other Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020))

The above is for January of 2020. For the other months, just change the 1 and 2020 to the month and year you want to count for.

Pamb10

Paul,

Thanks for your input. I did get the counts working correctly but found that the charts available in a Dashboard are not extensive enough for what I need to show. I need to show the numbers by month (ie. multiple series of data). I have accomplished what I need in Excel and have linked to the spreadsheet from my Dashboard.

Paul Newcome

I actually do this quite frequently with YTD metrics where I show monthly/weekly breakdowns of data. It should be possible within Smartsheet.

Pamb10

Maybe its just how I am trying to structure the data.

I thought about creating the sheet as follows:

jan feb mar

day1

day2

day3

But to do this i would need a formula to figure out the count for each cell in the sheet for the specific month/day. Seems like an awful lot of work. Do you have any ideas on how the sheet could be structured?

I also dont see how to get multiple data sets on the chart widget. I thought I read somewhere that could not be done. Maybe I am reading something outdated?

Interested if you have any thoughts or maybe resources on using the chart widget.

Thanks.

Pam

Paul Newcome

Honestly a lot of it is going to depend on you overall desired final outcome.

Were you wanting to build out your data similar to the screenshot below where the count for each day is in its own cell with rows being the day and columns being the month?

What exactly are you trying to show in the chart? Can you show a rough mock-up of what you want?

Pamb10

Well what i want to end up with is a line chart comparing month over month (daily) and a bar chart comparing monthly totals.

my thought is that to get this type of comparison in a chart I will need to have a column for each month. This is not how I was initially setting up the data.

Im beginning with a list of dates which I need to count per each day. So on the 1/1 the count is 10, on the 1/2 the count may be 5 and so on. My initial list of data is two columns as we discussed initially.

How can I get the data ifrom a two column sheet (1 row for each day of the year) to the format as above, a column for each month.

Paul Newcome

Ok. I think I may have some ideas.

Will you need to overlap years or can we assume that the dates will always be of the same year?

Pamb10

Same year.

Paul Newcome

Ok. So if you set up your sheet as below (making sure you have a column for each month and 31 numbered rows) we can use 2 very generic formulas that can be dragfilled to give you your counts, and the data will be laid out to allow for charting.

So row 2 is going to be our Monthly Totals. This formula goes in Jan2 and is dragfilled over for the remaining months:

=COUNTIFS({Other Sheet Date Column}, IFERROR(MONTH(@cell), 0) = Jan1)

Then the remaining rows are your daily totals. In the following formula, you will see two $ symbols. These lock in the column or row reference so that it can be dragfilled.

=COUNTIFS({Other Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = Jan$1, IFERROR(DAY(@cell), 0) = $Day@row))

Dragfill that across the remaining columns and down the remaining rows, and you should have your data in a matrix that can now be used in a chart on a dashboard.

Pamb10

Paul,

I just have to say this is so detailed and I am so thankful for your assistance. Thank you for your time to help me figure this out. Im off to give it a shot. Ive got so much to learn in SmartSheet. Formulas seem so difficult for me.

Although I cannot pay you anything, please have a virtual coffee on me!!! ☕️☕️☕️

Much appreciated!!!

Pam

Paul Newcome

Formulas certainly take some time to learn. They can definitely be a challenge.

Please let me know how it works out for you or if you need any further assistance. I am happy to help.

And since you're paying for the coffee... 😜

Pamb10

Paul,

This worked perfectly!!!!! No adjustments needed. Charts look great!

Thanks once again!

Drink Up!!!!!

Pam

Paul Newcome

Great! Happy to help! 👍️