Formula for Counting an Item Once Per Date

kodiwilder

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?

 

 

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