CODEWITHSUNDEEP
google-sheetscountcountif
Christian Gillum
Christian Gillum

Reputation: 1

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!

Upvotes: 0

Views: 116

Answers (1)

MattKing
MattKing

Reputation: 7773

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.

Upvotes: 1

Related Questions