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?

Find more posts tagged with

Formulas

Comments

L_123

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.

Paul Newcome

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)

L_123

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.

kodiwilder

That was extremely helpful and actually solved a few other issues too. Thank you!

Paul Newcome

Happy to help!

I love when a solution to one problem ends up helping find a solution to others. Haha

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…