excelexcel-formulaexcel-2010

Summing matching occurrences across an array, with maximum one count per row


I have an array like the below. I am wanting to create an output table for each unique column header (a,b,c), that counts for each row that there is a at least one TRUE.

a a b b b c c
TRUE TRUE TRUE TRUE TRUE FALSE FALSE
TRUE TRUE TRUE TRUE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE TRUE TRUE FALSE

The output for the array should look like this:

variable count
a 2
b 3
b 1

I have tried sum product, but it's counting every occurrence of TRUE per row, and not just once per row and unique column header.

The formula I've tried that's counting every TRUE occurrence instead of once per row is this: =SUMPRODUCT(($A$2:$E$2=A8)*($A$3:$E$5=TRUE)).


Solution

  • In Excel 2010 you could try using Mmult to get the row totals and see how many of them are greater than zero:

    =SUMPRODUCT(--(MMULT(($A$2:$G$2=A8)*($A$3:$G$5),TRANSPOSE(COLUMN($A$3:$G$5))^0)>0))
    

    enter image description here