Formula for 2 columns - checkbox and a date range

Asad Ullah

Hi there - newbie here. Looking for help with a formula.

I am trying to drive a metric which shows the percentage of activities on 'THD Brands' in the past week. The 'THD Brand' column is a checkbox, so essentially I need to count anything that is ticked in this column, and also the 'Last met?' column has a date within the last 7 days, divided by the overall count of all the activities that happened in the 7 days, whether they have a checkbox or not in the 'THD Brand' column.

Hopefully I have described that clearly enough ☺️

Asad Ullah

Thank you @Bassam Khalil - that worked!!

Bassam Khalil

@Asad Ullah

You are welcome and I will be happy to help you any time.

Bassam Khalil

@Asad Ullah

Please try the following formula, and don't hesitate to ask for any help. am at your service.

=IFERROR(COUNTIFS([Last met]:[Last met], AND(@cell >= TODAY(-7), @cell < TODAY())), "")

Bassam Khalil

Hi @Asad Ullah 

Hope you are fine, please try the following formula:

=IFERROR(COUNTIFS([HD Brand]:[HD Brand], 1, [Last met]:[Last met], @cell >= TODAY(-7)) / COUNTIFS([Last met]:[Last met], @cell >= TODAY(-7)), "")

screenshot shows the result:

Asad Ullah

Thank you @Bassam Khalil - that worked!!

Bassam Khalil

@Asad Ullah

You are welcome and I will be happy to help you any time.

Asad Ullah

@Bassam Khalil Can I be cheeky and ask for a formula for counting all dates within a column that happened in the last week? I need to count the number of 'Last Met' in total, regardless of THD Brand checkpoint

Bassam Khalil

@Asad Ullah

Please try the following formula, and don't hesitate to ask for any help. am at your service.

=IFERROR(COUNTIFS([Last met]:[Last met], AND(@cell >= TODAY(-7), @cell < TODAY())), "")

Asad Ullah

@Bassam Khalil - superstar - thank you!!!

Bassam Khalil

@Asad Ullah

I Am Glad that it's work with you.