Search code examples
excelarray-formulassumifs

excel extendable formula for conditional sums over multiple columns

I have an arbitrary number of columns, one for each period a course is offered, in chronological order, and an arbitrary number of rows, one for each unique participant. The values are '1' for participation in that month, '0' for non-participation.

Fall2019  Spring2019  Fall2018  Spring2018  Fall2017
1  1  0  1  0
0  1  1  1  1
0  1  1  1  1
0  1  1  1  1
0  1  0  1  1
0  1  1  0  0
0  1  1  0  0
0  1  1  0  0
1  0  0  0  0

I would like to take a sum, at the bottom of each column, for how many participants were first time attendees that period, i.e. the sum of '1's where all values in the row to the right of that '1', are '0'. In the given example set, Spring2018 should sum to 1, Fall2018 should sum to 3.

Something like the formula below will work for 'Spring2018' when there is just one previous column to compare:

=SUMPRODUCT((D2:D9)*(E2:E9=0))

But this formula cannot be 'autofilled' or extended across multiple columns... i.e. none of these variations work:

=SUMPRODUCT((C2:C9)*(D2:$E9=0))
=SUMPRODUCT((C2:C9)*(SUM(D2:$E9)=0))
=SUMPRODUCT((C2:C9)*(SUMIF(D2:$E9,"0")))

And while it will work, I do NOT want to have to manually create extended versions of this formula e.g.

=SUMPRODUCT((C2:C9)*(D2:D9+E2:E9=0))
=SUMPRODUCT((B2:B9)*(C2:C9+D2:D9+E2:E9=0))
... and so on

I have tried several variations on arrayformula, sumproduct, and sumif, but I'm really stuck. Any assistance is appreciated.

Solution

  • use this array formula:

    =SUM(A2:A10*(MMULT(--(B$2:$E$10=1),TRANSPOSE(COLUMN(B$2:$E$10)^0))=0))

    Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

    enter image description here