Extending ARRAYFORMULA in a column using a COUNTIF with horizontal ranges as parameter

0
votes

I have a sheet where I'd like to divide values listed in one column (col A) by a value of 1-6 depending on whether any of 6 conditions (in cols B - G) are true or false for the value in a given row.

Example screenshot here

I'm currently doing this with an individual formula per row, counting up the number of true conditions and then using that as the divisor. So for row 2, the formula would be:

=A2/COUNTIF((B2:G2),TRUE)

But I'm wondering if it's possible to get the job done with a single array formula that can be extended down through the rows. Something like:

=ARRAYFORMULA(A2:A27/COUNTIF((B2:G2),TRUE))

However, I can't find a way to make the COUNTIF formula work in this case ... it always uses the value for either just the first row or an entire array, rather then one row at a time for each row.

Hope that makes sense. Here is an example sheet to better visualize the issue.

Appreciate any input!

2
google-sheetsgoogle-sheets-formulaarray-formulas
What do you mean by But I'm wondering if it's possible to get the job done with a single array formula that can be extended down through the rows? Because using the =ARRAYFORMULA(A2/COUNTIF((B2:G2),TRUE)) and then with the mouse copying the function trough the rows works - Kessy
It's the "copying the function through the rows" that I'd like to avoid. The goal was to have only one formula in the first row of the column that'll then populate down the rows. - Demagus

2 Answers

0
votes

Inspired by https://stackoverflow.com/a/21804838/5851272

→ it seems this works: 

=ArrayFormula($A$2:$A$27/countifs(iF(COLUMN($B$2:$G$2),ROW($B$2:$B$27)),ROW($B$2:$B$27),$B$2:$G$27,TRUE))
0
votes

This is slightly simpler and should also work. =ARRAYFORMULA(IF(A2:A="",,A2:A/MMULT(N(B2:G),{1;1;1;1;1;1})))

here's an mmult demo i built a while back if you're interested. https://docs.google.com/spreadsheets/d/145JG2rqYMe6HSPnluppc7kIezVyWw6CpmC5GjLgb_yE/edit#gid=0