Total very fourth column-CodePudding

In Cell B3 I want to total up the 'x' values. For Driver #1 that would be cell F3, J3, N3, R3 and so on all the way to AN3 at the moment.

If it is easier I could convert wins 'x' to a 1 if that makes it easier.

CodePudding user response：

Try SUMPRODUCT:

=SUMPRODUCT(($F$2:$AN$2=B$2)*($F3:$AN3="x"))

CodePudding user response：

if you convert x to 1 try:

=SUM(FILTER(1*F3:AN3, MOD(COLUMN(F3:AN3)-2, 4)=0))

if not try:

=SUM(FILTER(IF(F3:AN3="x", 1, 0), MOD(COLUMN(F3:AN3)-2, 4)=0))

CodePudding user response：

You can use an array formula for this, and in such a way that if you add columns, it will "keep up" without any need to modify the formula. Because it is an array formula, there is no need to drag it down; one formula will produce all results for the range B3:B.

Delete everything from B3:B.

Then place the following formula in B3:

=ArrayFormula(IF(A3:A="",,MMULT((INDIRECT(ADDRESS(ROW(F3),COLUMN(F3))&":"&ROWS(A:A))="x") * (F2:2="Win"),SEQUENCE(COLUMNS(F2:2),1,1,0))))

IF(A3:A="",, just keeps any cells in B3:B null'blank if the corresponding cell in A3:A is null/blank.

INDIRECT(ADDRESS(ROW(F3),COLUMN(F3))&":"&ROWS(A:A)) creates a flexible range starting at F3 and including all cells to the lower rightmost cell in sheet, even if more rows or columns are added.

(INDIRECT(ADDRESS(ROW(F3),COLUMN(F3))&":"&ROWS(A:A))="x") * (F2:2="Win") checks to see if both of two conditions are met: that the cells in range contain "x" and that the value in the corresponding cell from Row 2 = "Win". If both are TRUE, then the result will be TRUE x TRUE or 1. If either or both is FALSE, the return will be 0 for that cell.

MMULT takes those 1s and 0s as matrix 1. The second matrix is formed from a stack of 1s as "tall" as there are columns in range. This stack is formed by SEQUENCE(COLUMNS(F2:2),1,1,0), where the parameters are as follows: number of rows, number of columns, starting number, increment number.

The multiplication of the 1s and 0s by a second matrix of all 1s results in the addition of each row of 1s and 0s (since anything x1 is itself, and based on the inherent functionality/properties of Matrix MULTiplication).