Search code examples
excelexcel-formulacountifsumifssumproduct

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

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!

Solution

  • 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