narnian_uk
New Member
- Joined
- Jul 28, 2021
- Messages
- 21
- Office Version
- 365
- Platform
- Windows
- MacOS
Hello - I'd be very grateful for any thoughts you might have re the following:
I have a range of data, D1:Q21. Column D contains IDs; the remaining columns contain 0s or 1s or blanks associated with those IDs. Duplicate IDs will occur (as in rows 3 and 12 below). I am trying to write a formula which will sum each column for each ID - so if, as in this example, ID "ghi" has one set of entries on row 3 which read 0, 0, 0, 0, 1, 0... and another set of entries on row 12 which read 1, 1, 1, 1, 1,..., the formula will produce 1, 1, 1, 2, 1... by adding the two rows together.
At the moment, I have a column S which uses the UNIQUE function to get a list of all IDs, and then individual formulae next to each ID in column T to produce the column sums for that ID:
(where $S1 becomes $S2 for the second ID, and so on). That works, but I'd rather have one cell containing a formula which produces the entire list of IDs and the sums for each ID because I never know how many IDs there will be in the original data. I tried this:
but it doesn't work, presumably because the condition in the FILTER function can't compare a column with an array.
Any thoughts?
I have a range of data, D1:Q21. Column D contains IDs; the remaining columns contain 0s or 1s or blanks associated with those IDs. Duplicate IDs will occur (as in rows 3 and 12 below). I am trying to write a formula which will sum each column for each ID - so if, as in this example, ID "ghi" has one set of entries on row 3 which read 0, 0, 0, 0, 1, 0... and another set of entries on row 12 which read 1, 1, 1, 1, 1,..., the formula will produce 1, 1, 1, 2, 1... by adding the two rows together.
At the moment, I have a column S which uses the UNIQUE function to get a list of all IDs, and then individual formulae next to each ID in column T to produce the column sums for that ID:
Excel Formula:
=LET(b,FILTER($F:$P,$D:$D=$S1,0),c, IF(ISNUMBER(b)=FALSE,0,b), MMULT(SEQUENCE(1,ROWS(c),1,0),--c))(where $S1 becomes $S2 for the second ID, and so on). That works, but I'd rather have one cell containing a formula which produces the entire list of IDs and the sums for each ID because I never know how many IDs there will be in the original data. I tried this:
Excel Formula:
=LET(a, UNIQUE(FILTER(D:D, D:D<>"")), b,FILTER($F:$P,$D:$D=a,0),c, IF(ISNUMBER(b)=FALSE,0,b), MMULT(SEQUENCE(1,ROWS(c),1,0),--c))but it doesn't work, presumably because the condition in the FILTER function can't compare a column with an array.
Any thoughts?
| Sample.xlsx | |||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | |||
| 1 | abc | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | abc | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | ||||||
| 2 | def | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | def | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | ||||||
| 3 | ghi | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | ghi | 1 | 1 | 1 | 2 | 1 | 0 | 2 | 2 | 2 | 1 | 2 | ||||||
| 4 | jklm | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 1 | jklm | 1 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 1 | ||||||
| 5 | nop | 1 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | nop | 1 | 0 | 0 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 0 | ||||||
| 6 | qrs | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1 | qrs | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1 | ||||||
| 7 | tuv | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | tuv | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | ||||||
| 8 | wxy | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 1 | 1 | wxy | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | ||||||
| 9 | z12 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | z12 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | ||||||
| 10 | 345 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 345 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | ||||||
| 11 | 678 | 1 | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 678 | 1 | 0 | 0 | 1 | 1 | 0 | 1 | 0 | 1 | 1 | 1 | ||||||
| 12 | ghi | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | a1b | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 0 | 1 | 1 | ||||||
| 13 | a1b | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | c2d | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 0 | 1 | 0 | ||||||
| 14 | c2d | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 0 | e3f | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | ||||||
| 15 | e3f | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | g4h | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | ||||||
| 16 | g4h | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | i5j | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | ||||||
| 17 | i5j | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | k6l | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | ||||||
| 18 | k6l | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | m7n | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | ||||||
| 19 | m7n | 0 | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 1 | o8p | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | ||||||
| 20 | o8p | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | q9r | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | ||||||
| 21 | q9r | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | ||||||||||||||||||
Sheet1 | |||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| S1:S20 | S1 | =UNIQUE(FILTER(D:D, D:D<>"")) |
| T1:AD20 | T1 | =LET(b,FILTER($F:$P,$D:$D=$S1,0),c, IF(ISNUMBER(b)=FALSE,0,b), MMULT(SEQUENCE(1,ROWS(c),1,0),--c)) |
| Dynamic array formulas. | ||
