0

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)

3
  • 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"). Nov 3, 2017 at 6:30
  • @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
    Nov 3, 2017 at 6:42
  • 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. Nov 3, 2017 at 7:55

2 Answers 2

3

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.

2
  • 1
    Just 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 Nov 3, 2017 at 17:56
  • 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. Nov 3, 2017 at 21:06
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .