从另一个工作表获取COUNT(数组公式) [英] Get COUNT (Array Formula) from another Sheet

问题描述

需要有关如何从工作到生产率工作表的计数的帮助:

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

1. 在生产率表(A2)中，获得在从日期到到日期 >

2. 在生产率表(B2，C2，D2，E2，F2，G2)中，在从日期到到日期工作

   中的日期按(A2)

3. 在"**工作表"中，计算工作表工作中有效(A2)

   的有效+无效

我有以下公式:

={"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))}

但是我希望它基于在另一个单元格上选择的时间和日期.此外，它不会附加为ARRAYFORMULA.

解决方案

我将使用MMULT()进行多层计数，就像您要尝试的那样. MMULT()是一种矩阵乘法，可用于像您这样的情况.

例如，该公式给出了所有4个原因"的计数:

=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})))})

此常规结构应该起作用，但是需要为日期开始和结束参数添加一个额外的条件.我不知道这样做是有意义的，直到您清除了我对原始帖子中有关混合数据类型的评论.

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.

解决方案

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.

这篇关于从另一个工作表获取COUNT(数组公式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！