# Formula for Counting an Item Once Per Date

Options
edited 12/09/19

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?

Tags:

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

Happy to help!

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!