Google sheets: Is there a way to quickly/easily add a date range to ~300 cells each with different countifs formulas?

Question

I am using the countifs function to add up a lot of different conditions - I need help to simplify the process so that it doesn't require so much manual formatting every time.

Here is a screenshot of a hypothetical spreadsheet. Here is a hypothetical scenario that will help convey my question. Let's say I am working with 3 clients, Macy's, abercrombie, and gap, to fill several open positions. We are reviewing multiple candidates. When I have reviewed them and approved, I select "yes" in the verdict column (E). When they have been processed, I selected yes in the F column. If I do not approve them, I select No in the column. So on and so forth.

So now I'd like to keep track of how many candidates I've approved and processed for each client for each open position. Here is my spreadsheet for that. I have used the countifs function from the previous spreadsheet, called "Review Document" as follows:

Column C, Row 2 - counting sales associate for abercrombie who have been approved and not yet processed:

=COUNTIFS(
    'Review Document'!$B:$B,"abercrombie", 
    'Review Document'!$C:$C, "sales associate",
    'Review Document'!$E:$E,"yes",
    'Review Document'!$F:$F,"no")

I essentially do this for every single client, for every single role, for both column C and D. Imagine that there are ~300 rows with different companies and roles - The formula text changes every time to count if "position" and "company".

What I would like to do is now find an easy way to automatically apply a date range to all of these cells, without having to manually add a date criterion for every single formula. For example, in the first spreadsheet, there are dates in Feb, Mar, And April. Is there a way to apply a date range on my second spreadsheet so that it only counts the dates I specify? E.G. - apply some date range to ALL cells in that sheet so that it only counts if the date is 2/15/2022-3/31-2022? I would ultimately like to be able to change the date range quickly without having to manually add a date criterion to 300 cells, and then change it every time I want to see the numbers for a different date range. I was tinkering with conditional formatting but I haven't figured it out.

Thanks!

Answer 1

use:

=INDEX(QUERY(QUERY({A2:A, PROPER(B2:C), 
 IF((E2:E="yes")*(F2:F<>"yes"), 1, 0), 
 IF((E2:E="yes")*(F2:F= "yes"), 1, 0)}, 
 "select Col2,Col3,sum(Col4),sum(Col5) 
  where Col1 is not null "&
 IF(J1="",," and Col1 >= date '"&TEXT(J1, "yyyy-mm-dd")&"'")&
 IF(J2="",," and Col1 <= date '"&TEXT(J2, "yyyy-mm-dd")&"'")&" 
  group by Col2,Col3"), 
 "offset 1", ))