Formula for Counting an Item Once Per Date
I am trying to come up with a formula to count the number of times a 'meeting type' occurs within a column. The issue is, I only want to count the 'meeting type' once per day whether it only occurs once or 20 times. So in the case of my screenshot, I would be returning the following: CIty Council - 3, SPIN - 2, P&Z - 2. Does anyone have a formula suggestion for this?
Comments
-
where do you want the results? It sounds like you want a countifs
=countifs([Meeting Type]:[Meeting Type],[Meeting Type]@row,[Meeting Date]:[Meeting Date],[Meeting Date]@row)
put that formula in the first row of a column and drag it down and you will get the numbers you want. there are prettier solutions though if you want something a with a little more jazz.
-
L@123,
I think we are trying to count how many days the meeting type appears. Not necessarily how many times per day.
I would suggest a helper checkbox column to flag the 1st appearance per day per meeting type. We can then use a COUNTIFS to see how many times that particular meeting type has been checked.
In the helper column I would use:
=IF(COUNTIFS([Meeting Type]$1:[Meeting Type]@row, [Meeting Type]@row, [Meeting Date]$1:[Meeting Date]@row, [Meeting Date]@row) = 1, 1)
Putting this in row 1 and dragfilling down will accomplish the first task.
The second part of this can be accomplished with a simple COUNTIFS formula along the lines of...
=COUNTIFS([Meeting Type]:[Meeting Type], "SPIN", [Helper Column]:[Helper Column], 1)
-
Right. My bad, misread the question. good catch. There is a way to do this in a single column, but it is drastically more complicated. I second Paul's solution.
-
That was extremely helpful and actually solved a few other issues too. Thank you!
-
Happy to help!
I love when a solution to one problem ends up helping find a solution to others. Haha
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!