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

0
votes

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!

1
excelexcel-formulacountifsumifssumproduct
But your example shows Source3 have value for other values like alpha, beta so what will be result for them. – Harun24HR
Can you show your expected result? – Harun24HR
@Harun24HR, you correctly interpreted the expected results--one value per column--in this case, 0,1,2. – JPC

1 Answers

1
votes

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))

enter image description here