Trying to COUNTIFS date = this week (sunday-saturday)?

I'm building a spreadsheet that automatically gets a row added when I get an application on my form. Here is the link to the sheet. As you can see, the first tab is just a list of applications, with the location they've applied for, and the date. The second tab is a daily count for each location, which is eventually sent out as an email each night. I'd like to include weekly numbers, and maybe even an ongoing weekly comparison. e.g. # of apps today, this week (so far), last week, etc.

I'm no expert with this stuff and it's getting a bit over my head possibly. Any ideas on how to get this done smoothly?? Thanks a ton in advance!

1 answer

  • answered 2021-05-03 18:24 MattKing

    Your problem (like many) is primarily a problem with the organization of your raw data.

    On a new tab called MK.Help, I've put the following formula in cell A2:

    =ARRAYFORMULA(QUERY(SPLIT(FLATTEN(Applications!A2:A&"|"&Applications!S2:S&"|"&FLOOR(Applications!S2:S-2,7)+2&"|"&Applications!B2:R),"|",0,0),"where Col4 is not null and Col2>="&I2))
    

    Then I made a simple table on the right with some fairly straightforward COUNTIFS() that look like this:

    =COUNTIFS($C:$C,H$2,$D:$D,$F3)
    

    The layout of the data vertically is what's making the formulas relatively simple for the summary.