Combining ArrayFormula and COUNTIF for date-specific project totals in Spreadsheets

Other Questions Google Sheets
1

Hey everyone, I’m trying to figure out how to merge two formulas in my spreadsheet. Right now I’ve got one that counts yesterday’s projects:

=ArrayFormula(SUM(1*(INT(Archive!M3:M)=Today()-1)))

And another that tallies specific project types:

=COUNTIF('V1 Archive'!D:D,"25A")

What I’m aiming for is a way to count specific project types from yesterday only. My project codes are text (like 25A, 25B, 25C) and dates are in the format 6/3/2014 16:41:36.

Any ideas on how to combine these or create a new formula that does both? Thanks!

2

I’ve wrestled with similar spreadsheet challenges before, and I think I’ve got a solution that might work for you:

=QUERY(‘V1 Archive’!C:M, “SELECT COUNT(D) WHERE D = ‘25A’ AND DATE(M) = DATE '” & TEXT(TODAY()-1, “yyyy-mm-dd”) & “'”, 0)

This QUERY function does a couple of things: it filters for your specific project type (25A in this case) and checks for yesterday’s date. The beauty of this approach is its flexibility; you can easily change the project type or adjust the date range. Plus, it’s generally faster than array formulas when dealing with large datasets.

Just make sure your date column (M) is formatted as dates. If they’re text, you might need to wrap the M column reference in DATEVALUE().

Let me know if you run into any snags!

3

hey there mikezhang! you could try somethin like this:

=COUNTIFS(‘V1 Archive’!D:D,“25A”,‘V1 Archive’!M:M,“>=”&INT(TODAY()-1),‘V1 Archive’!M:M,“<”&INT(TODAY()))

this should count 25A projects from yesterday. adjust the project code n column references as needed. lemme know if it works!

4

I’ve encountered a similar challenge in my work. Here’s a solution that might help:

=ARRAYFORMULA(SUM((INT(‘V1 Archive’!M:M)=TODAY()-1)*(‘V1 Archive’!D:D=“25A”)))

This formula combines the date check and project type filter. It evaluates each row, checking if the date in column M is yesterday and if the project code in column D matches “25A”. The result is a count of matching entries.

You can easily modify the project code or date criteria as needed. For instance, to count projects from the last 7 days, replace TODAY()-1 with TODAY()-7.

Hope this helps solve your problem efficiently.