COUNTIF between date range and 2 criteria
I am trying to cross reference another sheet to count all of the projects assigned to a specific person in between a certain range, but the # of projects assigned and the person assigned to the project are 2 separate criteria.
For example, I want to know how many "A" projects that Jane completed in the month of January.
I have tried a handful of different formula combinations and I've done some digging through past community questions but I'm not quite sure where to even start from anymore lol.
Does anyone have a solution?
Any and all help is appreciated!
Thank you!!
Best Answer
-
You're going to need something along these lines...
=COUNTIFS({Assigned To}, @cell = "Jane", {Project Type}, @cell = "A", {Date}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2022))
Answers
-
You're going to need something along these lines...
=COUNTIFS({Assigned To}, @cell = "Jane", {Project Type}, @cell = "A", {Date}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2022))
-
@Paul Newcome - this worked, thank you so much!!
-
Happy to help. 👍️
-
Hi, I am having similar issues as the one resolved above and wonder if @Paul Newcome or anyone can guide me on where i am going wrong please, the two different variations i have tried (&both failed) are:
=COUNTIFS({AML Priority}, "High", {JTCL - Child / Parent Helper Row}, "Action", AND{Start Date}<= DATE(2024, 5, 1), NETWORKDAYS({Start Date}, MIN(DATE(2024, 9, 30),{End Date})) / NETWORKDAYS({Start Date}, {End Date})), ""))
=COUNTIFS({AML Priority}, "High", {JTCL - Child / Parent Helper Row}, "Action"), AND(IF({Start Date}:{Start Date}, >=DATE(2024, 5, 1), {End Date}:{End Date}, <=DATE(2024, 9, 30)))
I am looking to capture all items that are 'high' that have an end date between 01 May 2024 and 30 Sept 2024
Thanks in advance
-
@JamesQ4 You don't need an AND function because you are using two different {Ranges}. The AND is implied when using a COUNTIFS, so you can just follow the standard syntax of {Range} comma criteria comma {Range} comma criteria comma etc….
-
Hi @Paul Newcome - I'm using the same formula layout as originally mentioned above but am receiving the #INCORRECT ARGUMENT SET error. Any thoughts on the issue here?
Trying to capture how many CDs are in Range 1 and that are Ground Up in Range 4 per month.
=COUNTIFS({TRACKING SHEET Range 1}, "CD", {TRACKING SHEET Range 2}, "GROUND UP", {TRACKING SHEET Range 3}, AND(IFERROR(MONTH(@cell ), =1, IFERROR(YEAR(@cell ), =2024))))
-
@aecross Make sure to remove the spaces after the @cell references, and you are also missing the final portion of each of the IFERROR functions.
IFERROR(MONTH(@cell), 0)
-
@Paul Newcome where is the correct spot for the 0? the below gives #UNPARSEABLE
AND(IFERROR(MONTH(@cell) 0), =1, IFERROR(YEAR(@cell) 0), =2024))
-
Your commas are misplaced.
IFERROR(MONTH(@cell), 0) = 1
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!