# Formula for 2 columns - checkbox and a date range

Options
edited 06/09/21

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 ☺️

Options

Thank you @Bassam Khalil - that worked!!

• ✭✭✭✭✭✭
Options

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

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

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

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
edited 06/09/21
Options

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:

bassam.khalil2009@gmail.com

Options

Thank you @Bassam Khalil - that worked!!

• ✭✭✭✭✭✭
Options

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

bassam.khalil2009@gmail.com

• Options

@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

• ✭✭✭✭✭✭
Options

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

bassam.khalil2009@gmail.com

• Options

@Bassam Khalil - superstar - thank you!!!

• ✭✭✭✭✭✭
Options

I Am Glad that it's work with you.

bassam.khalil2009@gmail.com