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

1. In Productivity Sheet (A2), get unique names that worked within the time of From Date to To Date

2. 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

3. 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.

Answer 1

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.