Formula for Counting an Item Once Per Date

kodiwilder
edited 12/09/19 in Formulas and Functions

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?

 

 

Capture.PNG

Tags:

Comments

  • L_123
    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
    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
    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.

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

     

    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!