Excel counting taking into account adjacent cells (summing along a column, but looking across a row)

Question

I have a table that tells me whether a value is found in a source:

(image of excel cells)

Value	Source1	Source2	Source3
alpha	1	0	1
beta	0	1	1
gamma	1	0	0
delta	1	1	1
epsilon	0	1	0
zeta	0	1	0

What I'd like to do is count the number of times that each source uniquely finds a given value. For this example, there are:

• one value unique to Source1 (gamma)
• two values unique to Source2 (epsilon and zeta)
• zero values unique to Source3

In practice, this calculation will be used on ~10 columns and 1000s of rows, so I need some formula help.

I've tried various combinations of sumifs, countifs, sumproducts, and array formulas, but I am stumped by the fact that the sum needs to look perpendicularly to the column.

Any help is much appreciated!

Answer 1

With Excel365 you can try below formula-

=SUM(--(MMULT($B$2:$D$7,SEQUENCE(COLUMNS($B$2:$D$2),,,0))*(B$2:B$7)=1))

For Non365 version of excel you try below array (CTRL+SHIFT+ENTER) formula. In this case you must enter same number of one 1 of source column.

=SUM(--(MMULT($B$2:$D$7,TRANSPOSE({1,1,1}))*(B$2:B$7)=1))