0

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.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
KB John
  • 89
  • 5
  • 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 Aug 31 '20 at 18:59

1 Answers1

2

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.

MattKing
  • 7,373
  • 8
  • 13
  • Thanks for your input @MattKing. Yes, that's what I can't figure out the date and time part... – KB John Sep 01 '20 at 04:50
  • 1
    Right, i'm saying i can do the date and time part. but that your column of date times cannot have TEXT words in it like "DUPLICATE" if you don't want duplicates, i can do that formulaically – MattKing Sep 01 '20 at 13:23
  • 1
    @KBJohn The formula on the MK.Help tab is done and it is counting between the date thresholds – MattKing Sep 01 '20 at 13:28
  • Thanks @MattKing Could you help me out in fixing Column A to show only those emp who worked within the day selected as well? Also, Reason 1-4 aren't really the original choices. In the original file those are composed of 10 different items. – KB John Sep 01 '20 at 16:53
  • 1
    It's done. ={"By:";UNIQUE(FILTER(Worked!P2:P,Worked!Q2:Q>=B1,Worked!Q2:Q<=D1))} – MattKing Sep 01 '20 at 16:54
  • Hi @MattKing I forgot to add, the sheet should only be counting those that have valid and invalid under status column. Any other status shouldn't be counted. – KB John Sep 02 '20 at 06:05
  • @KBJohn A little bit too much mission creep on this one for me. Feel free to repost if your question hasn't been answered, but I can't spend too much more time on it. You can find me on reddit u/MattyPKing – MattKing Sep 02 '20 at 17:48