Get COUNT (Array Formula) from another Sheet

Question

Need help on how to get the count from Worked to Productivity sheet:

https://docs.google.com/spreadsheets/d/1c3HdMq4PA50pYr88JqPoG51jvru8ipp8ebe4z5DczTQ/edit#gid=1891370548

In Productivity Sheet (A2), get unique names that worked within the time of From Date to To Date
In Productivity Sheet (B2,C2,D2,E2,F2,G2), get count within the time of From Date to To Date By (A2) from sheet Worked
In **Productivity Sheet, count how many Valid + Invalid from sheet Worked By (A2)

I have the following formulas:

={"By"; unique(query(Worked!A2:Q,"select P where Q >= datetime '"&TEXT($B$1,"yyyy-mm-dd HH:mm:ss")&"'"))}
={"Reason 1"; ARRAYFORMULA(COUNTIFS(Worked!F2:F,"1"))}
={"Reason 2"; ARRAYFORMULA(COUNTIFS(Worked!F2:F,"2"))}
={"Reason 3"; ARRAYFORMULA(COUNTIFS(Worked!F2:F,"3"))}
={"Reason 4"; ARRAYFORMULA(COUNTIFS(Worked!F2:F,"4"))}
={"High"; ARRAYFORMULA(COUNTIFS(Worked!C2:C,"High"))}
={"Normal"; ARRAYFORMULA(COUNTIFS(Worked!C2:C,"Normal"))}
={"Total Worked"; ARRAYFORMULA(SUM(F3:G3))}

But I want it to be based on time and date selected on another cell. Also it doesn't append as ARRAYFORMULA.

The first change you'll need to make if you want to use QUERY() functions is that column Q on your worked tab cannot have the word "DUPLICATE" mixed in with the timestamps. It's not clear from your example if that's happening manually or as the result of some other function, but query() won't work when you mix numbers and text in the same column like that. Is that asomething you're prepared to part with?/change? — MattKing

MattKing MattKing · Accepted Answer · 2020-08-31T19:25:34

I would use an MMULT() for multi-tiered countifs like you're trying to do. MMULT() is a kind of matrix multiplication that's useful for situations like yours.

For example, this formula gives the counts for all 4 "reasons":

=ARRAYFORMULA({"Reason "&{1,2,3,4};IF(A3:A="",,MMULT(N(A3:A=TRANSPOSE(Worked!P:P)),N(Worked!F:F={1,2,3,4})))})

This general structure should work, but needs an extra condition added for the date start and end parameters. I don't know that it makes sense to do that until you've cleared up the comment i made on your original post about mixed data types.

Get COUNT (Array Formula) from another Sheet

1 Answers