countifs com linhas somadas comparadas a uma média

Question

Estou tendo alguma dificuldade em descobrir uma maneira de fazer isso funcionar no Planilhas Google.

Eu estou olhando para usar um countif (ou função similar) para contar linhas em uma folha onde a coluna B: B + C: C + D: D > sheet2! $ A $ 2. A função deve contar apenas as linhas que correspondem aos critérios de sheet2! $ B $ 3 dentro da célula A: A, bem como alguns outros critérios, com o mesmo formato, apenas células diferentes, então presumo que um countifs seja provavelmente a melhor opção?

Minha abordagem atual é algo semelhante ao abaixo (ive tentou variações), mas não consigo encontrar uma maneira de fazê-lo considerar cada linha individualmente.

= countifs (folha1! B: B + folha2! C: C + folha3! D: D, > H2)

    

Answer 1

Muito bom, @barry.

O Arrayformula () é exclusivo do Planilhas Google, eu acho. Então, para usuários do Excel, adicionarei esta solução:

=SUMPRODUCT(1*((Sheet1!B:B+Sheet2!C:C+Sheet3!D:D)>Sheet2!$A$2)*(Sheet2!A:A=Sheet2!$B$3))

A expressão interna (Sheet1!A:A+Sheet2!B:B+Sheet3!C:C)>H2) produz uma matriz de valores True / False com True, em que a soma das linhas nas três colunas referenciadas é maior que A2. Multiplicar por 1 converte os valores de Verdadeiro / Falso para 1 e 0 e, em seguida, SUMPRODUCT () os adiciona, além de eliminar a necessidade de inserir a fórmula como uma fórmula de matriz.

Outros critérios podem ser incluídos multiplicando-se a matriz interna por outra matriz semelhante de valores True / False, como no final da fórmula, onde a matriz final especifica que Sheet2! A: A deve ser igual a $ B $ 3 para a linha para ser contado.

    

Answer 2

Você não pode usar COUNTIFS porque, como no Excel, os critérios ranges não podem ser arrays e, portanto, não podem ser modificados por funções ou cálculos.

Tente isso no google sheets

=arrayformula(sum((Sheet1!A:A=Sheet2!$B$3)*(Sheet1!B:B+Sheet1!C:C+Sheet1!D:D>Sheet2!$A$2)))

Você pode adicionar mais critérios conforme necessário