Count non blank values by category-CodePudding

I would like to count of occurences where cells aren't blank for each category (placed in rows)

Sample Data set:

Output:

EDIT:

I would like the formula inside the column col to reflect whatever value is on the left in the color col. In other words if I change blue to orange, the count should update as well.

CodePudding user response：

SUMPRODUCT:

=SUMPRODUCT(($B$2:$D$4<>"")*($A$2:$A$4=H2))

COUNTIF()(only if they are truly null):

=COUNTIF(INDEX($B$2:$D$4,MATCH(H2,$A$2:$A$4,0),0),"<>")

COUNTA(same as COUNTIF, Has to be null)

=COUNTA(INDEX($B$2:$D$4,MATCH(H2,$A$2:$A$4,0),0))

Or COUNT(if they are all numbers like your example:

=COUNT(INDEX($B$2:$D$4,MATCH(H2,$A$2:$A$4,0))

With Office 365 the INDEX/MATCH in the bottom 2 can be replaced with FILTER(COUNTIF requires a range not an array:

FILTER($B$2:$D$4,$A$2:$A$4=H2)