Dashboard
Hello everyone,
I am trying to make my first Dashboard. On my data sheet, I have dates from Jan 1 2021-March 31 20201. Instead of showing all individual days, How can I make it only show month like January, February, and March to summarize data monthly?
Thanks,
Bryan
Best Answer
-
Does the {Date} range contain actual date values in a date type column?
If not, try correcting that first. If so or if you still continue to get that particular error, try this little tweak...
=COUNTIFS({Date}, IFERROR(MONTH(@cell), 0) = MonthNo@row, {Indicator}, 1)
The IFERROR will account for blanks and non-date values within the date range so that the COUNTIFS can function properly.
Answers
-
Hi Bryan,
What I generally do is set up a separate sheet with formulas (with cross-sheet references) to summarize the data I want to display. To go further into detail, you may want to provide screenshots of your source data (with any sensitive material blacked out), and describe what kind of widgets you are using (metrics, chart, report, etc.).
Thanks,
Heather
-
Hi Heather,
Thank you so much for answering my question. I want to use Chart to display how many redflags that each month has. I was trying to do it on a different sheet to summarize the data, but I am struggling with the formula.
Thanks for your help
Bryan
-
I see one thing that may cause you problems with your source sheet - your primary column is the one you're using for dates. In order for formulas to work with dates, you will want to assign a different column as your primary column. You could even create a new column, leave it blank, set it as the primary column, and hide it. Then you'll want to right click on the current primary column, go into column properties, and set it as a Date column.
Here's how I would set the summary sheet up:
Month - type in one month for each row (January-December)
MonthNo - type in 1 through 12 (in the January row, type 1, July is 7, December is 12, etc.)
Count - use this formula: =countifs({date},@cell=MONTH(MonthNo@row),{Indicator},1)
-- When you're setting up the formula and you get to =countifs( you'll want to then click the Reference Another Sheet link, look up the source sheet, and select the column you would like to use. When you do this, it will come up with an automatically-generated name. It's best practice to name your range in the box above where the columns are listed (see screenshot below). You'll have to repeat it again for the Indicator column.
Named ranges show up in {brackets}, so I've named your source sheet column that is currently called "Primary Column" {date}, and "Cold Blue Indicator" range is named {indicator}.
From there, when you set up your chart widget, you'll be able to select the summary sheet, and select all three columns of data. You can then go into the chart options and edit which columns you want to include in your chart, so you can take out the MonthNo column.
Hope this helps! Let me know if you have any questions.
Best,
Heather
-
HI Heather,
When I type in the Countifs function and I get #invalid data type. I did change the primary column to blank and added another column for date. here is my summary sheet. what did I do wrong?
Thank you.
Bryan
-
Hi Bryan, did you name your cross-reference ranges as mentioned in my post (by clicking the Reference Another Sheet link)?
-
Hi Heather,
Yes I did
-
-
The issue is with the date criteria.
=countifs({date},@cell=MONTH(MonthNo@row),{Indicator},1)
Try this instead..
=countifs({date},MONTH(@cell)=MonthNo@row,{Indicator},1)
-
@Paul Newcome Thanks for the assist!
-
Hi Paul,I tried your formula and it's still giving me #invalid data type
Thanks,
-
Does the {Date} range contain actual date values in a date type column?
If not, try correcting that first. If so or if you still continue to get that particular error, try this little tweak...
=COUNTIFS({Date}, IFERROR(MONTH(@cell), 0) = MonthNo@row, {Indicator}, 1)
The IFERROR will account for blanks and non-date values within the date range so that the COUNTIFS can function properly.
-
Thank you so much. The problem is because of the blanks that I have for unused rows.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!