countifs with summed rows compared to an average

I'm having some difficulty in figuring out a way to make this work in Google Sheets.

I'm looking to use a countif (or similar function) to count rows in a sheet where column B:B+C:C+D:D > sheet2!$A$2. The function should only count rows that match the criteria of sheet2!$B$3 within cell A:A, as well as a couple other criteria, with the same format, just different cells, so I assume a countifs is probably the best option?

My current approach is something similar to below (ive tried variations), but I can't seem to find a way to make it consider each row individually.

=countifs(sheet1!B:B+sheet2!C:C+sheet3!D:D,>H2)

NRGdallas

Posted 2017-11-03T01:35:46.117

Reputation: 111

Not sure what "B2:B" means, please edit your question to give some sample data and the outcome you wish to have plus the exact formula you have already (not "something similar to below"). – Kevin Anthony Oppegaard Rose – 2017-11-03T06:30:18.897

@KevinAnthonyOppegaardRose Edited, B2:B in google sheets basically just takes everything from B2 to the end of the column, ignoring B1, but for all purposes they are identical. - As for my current approach, I've tried countifs, sumifs, etc - nothing seems to work, its a bit beyond my knowledge really – NRGdallas – 2017-11-03T06:42:46.143

So, you want to count the number of occasions An+Bn+Cn>Dn, where n is the row number, for all rows. To do this you need a "FOR EACH" function, I dont think you can do this without VBA. – Kevin Anthony Oppegaard Rose – 2017-11-03T07:55:53.253

Answers

Very nice, @barry.

Arrayformula() is unique to Google Sheets, I think. So for Excel users, I'll add this solution:

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

The inner expression (Sheet1!A:A+Sheet2!B:B+Sheet3!C:C)>H2) produces an array of True/False values with True where the sum of the rows in the 3 referenced columns is greater than A2. Multiplying by 1 converts the True/False values to 1's and 0's, and then SUMPRODUCT() adds them up, while also eliminating the need to enter the formula as an array formula.

Other criteria can be included by multiplying the inner array by another, similar array of True/False values, as at the end of the formula where the final array specifies that Sheet2!A:A must equal $B$3 for the row to be counted.

Bandersnatch

Posted 2017-11-03T01:35:46.117

Reputation: 3 430

1Just a small point - as written the 1* is redundant and can be removed because the * between the conditions will do the necessary coercion - but good answer for both excel and google – barry houdini – 2017-11-03T17:56:11.920

Good point. I added the last array after I read the question properly and noticed the extra constraint about Column A being equal to B3. I could have taken out the 1* at that point. I've noticed that some use two minus signs to accomplish the same thing. – Bandersnatch – 2017-11-03T21:06:53.443

You can't use COUNTIFS because as with Excel the criteria ranges can't be arrays and therefore can't be modified by functions or calculations.

Try this for google sheets

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

You can add more criteria as required

barry houdini

Posted 2017-11-03T01:35:46.117

Reputation: 10 434