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
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!