Trying to pull data within a date range - need assistance with the formula
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))
Answers
-
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.
-
Thanks, Hollie - I tried that formula and it still didn't work.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I had a coworker take a look at this, and he was able to work it out. Thanks for the help!
-
@mlpugh can you possibly share your resulting formula that worked for you?
TIA
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!