How to count and sum of numbers separated by comma in multiple cells of a row in excel?

I got 11 & 10 for 1st row (instead of 10 & 9) and 7 & 6 for 2nd row (instead of 13 & 11)
In Z9:

Please Login or Register  to view this content.

Drag down and accross.

In which (for instant row 9, case: Visit):
Sumproduct: count cells visit those are ocupied = 6 (month 1, 2, 4, 5, 9, 12)
Countifs: count number of "," in cells visit. = 5 (1,2,5,9,12)
Total: 11

Hi,

Assuming all values are only ever single-digit:

In Z9, count for row 9:

=SUMPRODUCT(N((B$8:Y$8=Z$8)*MID(B9:Y9&"000",{1;3},1)>0))

and copied across.

In Z10, sum for row 10:

=SUMPRODUCT((B$8:Y$8=Z$8)*MID(B10:Y10&"000",{1;3},1))

Regards

Originally Posted by XOR LX

In Z9, count for row 9:
and copied across.
In Z10, sum for row 10:
Regards

Is it a single formula in Z9 then copy down and accross?

Originally Posted by bebo021999

Is it a single formula in Z9 then copy down and accross?

No, as I said it's two separate formulas. That's what the OP asked for if you look at his workbook. Not sure why he wants it that way!

Regards

My formula in #4 works for data like this:
1,2
3,4
10,5
12,10
it is counting cell with data and each comma "," add 1