COUNTIF between date range and 2 criteria

aecross

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!!

Paul Newcome

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

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))

aecross

@Paul Newcome - this worked, thank you so much!!

Paul Newcome

Happy to help. 👍️

JamesQ4

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

Paul Newcome

@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….

aecross

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))))

Paul Newcome

@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)

aecross

@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))

Paul Newcome

Your commas are misplaced.

IFERROR(MONTH(@cell), 0) = 1

SkiPatrolScott

@Paul Newcome Paul can you help (of course you can) but will you help, me close this loop

=Countifs(Date:Date, @cell >= Date(2023,2,1), Activity:Activity, @cell= "Violation") how do I create a inbetween date say Date(2023,2,28)

What I am trying to do is use the Summary Sheet to isolate date ranges. Otherwise we will need a new sheet each season but it would be good to have a continuous sheet to pull seasonal data on the fly…

Paul Newcome

@SkiPatrolScott You would use an AND function for the criteria.

AND(@cell >= DATE(yyyy, mm, dd), @cell <= DATE(yyyy, mm, dd))

SkiPatrolScott

@Paul Newcome I am not sure where to insert the formula you provided? I tried to remove my DATE(yyyy,mm,dd) but got an error message…

Also I think you know, but the Date:Date is a field label, I should probably rename so it doesnt cause a confuse

Paul Newcome

@SkiPatrolScott That is just the criteria for your date range.

=COUNTIFS({Date Range}, AND(………………..), …………………………)