Count Formula Help

B Young

I'm new to formulas and referencing other sheets in SmartSheet and need help with the following scenario:

We are wanting to reference another sheet to sum the number of days a team member is absent in a period of time. The referencing sheet has the columns of [Date Absent] and [Team Member].

The sheet where the formulas are has the columns [Start of Month] and [Last Day of Month] and then [Team Member 1] [Team Member 2] [Team Member 3] and so on in the proceeding columns.

So the formula in [Team Member 1] needs to read something like: count the number of times team member 1 appears in [Team member] column if the [Date Absent] is between [Start of Month] and [Last Day of Month]

Thanks for any help!

Shubham

Hi B Young 

I hope you're doing well. 

According to your requirements you can use this formula. 

=COUNTIFS({Date Absent}, [Start of Month]@row >= [Start of Month]@row, {Date Absent}, [Last Day of Month]@row <= [Last Day of Month]@row, {Team Member}, "Team Member 1") 

 

I hope this helps you. Have a Good Day. 

Thanks  

Shubham Umale, Smartsheet Engineer, Ignatiuz Software 

Shubham

Hi B Young 

I hope you're doing well. 

According to your requirements you can use this formula. 

=COUNTIFS({Date Absent}, [Start of Month]@row >= [Start of Month]@row, {Date Absent}, [Last Day of Month]@row <= [Last Day of Month]@row, {Team Member}, "Team Member 1") 

 

I hope this helps you. Have a Good Day. 

Thanks  

Shubham Umale, Smartsheet Engineer, Ignatiuz Software 

B Young

Thank you this is excellent! Appreciate your help and prompt response.