Trying to pull data within a date range - need assistance with the formula

mlpugh

I'm attempting to collect data across sheets and am having a hard time getting my formula right. I want to collect the number of sites in a program that falls into a particular date range. So, I want to pull from our intake sheet all sites in Program A that are due between 1/9/23 and 1/13/23.

I've tried a couple of different formulas (see below) with no luck. I welcome any feedback or suggestions on a formula to try.

=COUNTIF({Program Name: Program Name}, "Program A" and (COUNTIFS({Due Date:Due Date}, <=DATE(2023, 1, 13), {Due Date:Due Date}, >=DATE(2023, 1, 9)))

=SUM(COUNTIFS({Program Name: Program Name}, "Program A", {Due Date:Due Date, <=DATE(2023, 1, 13), {Due Date:Due Date}, >=DATE(2023, 1, 9}))

=COUNTIFS({Program Name: Program Name}, "Program A", {Due Date:Due Date}, <=DATE(2023, 1, 13), {Due Date:Due Date}, >=DATE(2023, 1, 9))

Hollie Green

Try the below formula it should count all the times Program A occurs with a due date between the dates listed.

=Countifs({Program Name},{Program Name},"Program A",{Due Date},<=Date(2023,1,13),{Due Date},>=Date(2023,1,9).

You will have to set up your reference for the Program Name Column and the Due Date column. When referencing another sheet you don't duplicate the name of the column to count the entire column you set up the reference as a column reference.

mlpugh

Thanks, Hollie - I tried that formula and it still didn't work.

Genevieve P.

Hi @mlpugh

It looks like one of the ranges was duplicated in Hollie's formula, try this:

=COUNTIFS({Program Name}, "Program A", {Due Date},<=Date(2023,1,13), {Due Date},>=Date(2023,1,9))

If it doesn't work for you, can you let us know if you're getting an error or an incorrect result? It would also be helpful to see a screen capture of your sheet with the formula opened up in the cell.

Thanks!

Genevieve

mlpugh

I had a coworker take a look at this, and he was able to work it out. Thanks for the help!