For the month of January, there is one customer, aptly named "ONE" that has both A and B services. Therefore, "Both" = 1.
Likewise, in the same month, there is one customer, named "TWO" that has only the A service. Therefore A = 1.
Overall, there are two paying customers this month: customer ONE and customer TWO. Therefore Total = 2.
[TABLE="width: 690"]
<colgroup><col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2787;" width="78"> <col style="width: 55pt; mso-width-source: userset; mso-width-alt: 2588;" width="73"> <col style="width: 48pt;" span="12" width="64"> <tbody>[TR]
[TD="class: xl66, width: 78, bgcolor: transparent"]
Company[/TD]
[TD="class: xl66, width: 73, bgcolor: transparent"]
Service[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
3[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
5[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
6[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
7[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
8[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
9[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
10[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
11[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
12[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 78, bgcolor: transparent"]
ONE[/TD]
[TD="class: xl64, width: 73, bgcolor: transparent"]
A[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, width: 78, bgcolor: transparent"]
ONE[/TD]
[TD="class: xl64, width: 73, bgcolor: transparent"]
B[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, width: 78, bgcolor: transparent"]
TWO[/TD]
[TD="class: xl64, width: 73, bgcolor: transparent"]
A[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 78, bgcolor: transparent"]
THREE[/TD]
[TD="class: xl64, width: 73, bgcolor: transparent"]
B[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 78, bgcolor: transparent"]
FOUR[/TD]
[TD="class: xl64, width: 73, bgcolor: transparent"]
A[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 78, bgcolor: transparent"]
FOUR[/TD]
[TD="class: xl64, width: 73, bgcolor: transparent"]
B[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 78, bgcolor: transparent"]
ONE[/TD]
[TD="class: xl64, width: 73, bgcolor: transparent"]
A[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
100[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
3[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
5[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
6[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
7[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
8[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
9[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
10[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
11[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]
12[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 78, bgcolor: transparent"]
A[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 78, bgcolor: transparent"]
B[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
0[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
0[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
0[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
0[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 78, bgcolor: transparent"]
Both[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
0[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
0[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
0[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
0[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
1[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 78, bgcolor: transparent"]
Total[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
2[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
3[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
3[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
3[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
3[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
4[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
4[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
4[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]
4[/TD]
[/TR]
</tbody>[/TABLE]
C11, control+shift+enter, not just enter, and copy across:
=SUM(IF(FREQUENCY(IF(IF(ISNUMBER(C$2:C$8),$A$2:$A$8,"")<>"",IF(ISNA(MATCH(IF(ISNUMBER(C$2:C$8),$A$2:$A$8,""),IF(IF(ISNUMBER(C$2:C$8),$B$2:$B$8,"")="B",IF(ISNUMBER(C$2:C$8),$A$2:$A$8,"")),0)),MATCH(IF(ISNUMBER(C$2:C$8),$A$2:$A$8),IF(ISNUMBER(C$2:C$8),$A$2:$A$8),0))),ROW($A$1:$A$8)-ROW($A$1)+1),1))
C12, control+shift+enter and copy across:
=SUM(IF(FREQUENCY(IF(IF(ISNUMBER(C$2:C$8),$A$2:$A$8,"")<>"",IF(ISNA(MATCH(IF(ISNUMBER(C$2:C$8),$A$2:$A$8,""),IF(IF(ISNUMBER(C$2:C$8),$B$2:$B$8,"")="A",IF(ISNUMBER(C$2:C$8),$A$2:$A$8,"")),0)),MATCH(IF(ISNUMBER(C$2:C$8),$A$2:$A$8),IF(ISNUMBER(C$2:C$8),$A$2:$A$8),0))),ROW($A$1:$A$8)-ROW($A$1)+1),1))
C13, control+shift+enter and copy across:
=SUM(IF(FREQUENCY(IF(IF(ISNUMBER(C$2:C$8),$A$2:$A$8,"")<>"",IF(ISNUMBER(MATCH(IF(ISNUMBER(C$2:C$8),IF($B$2:$B$8="A",$A$2:$A$8,"#"),"#"),IF(ISNUMBER(C$2:C$8),IF($B$2:$B$8="B",$A$2:$A$8)),0)),MATCH(IF(ISNUMBER(C$2:C$8),$A$2:$A$8),IF(ISNUMBER(C$2:C$8),$A$2:$A$8),0))),ROW($A$2:$A$8)-ROW($A$1)+1),1))
C14, control+shift+enter and copy across:
=SUM(IF(FREQUENCY(IF(IF(ISNUMBER(C$2:C$8),$A$2:$A$8,"")<>"",MATCH(IF(ISNUMBER(C$2:C$8),$A$2:$A$8),IF(ISNUMBER(C$2:C$8),$A$2:$A$8),0)),ROW($A$1:$A$8)-ROW($A$1)+1),1))
Note 1. These formulas cannot be implemented on pre-2007 systems because of the nesting levels they have.
Note 2. It would be very hard to expand the set of services.
Note 3. If so desired, one repetitive part of these formulas can be replaced using S. Dunn's VBA coded function V().