Counting and summing based on 2 columns

I

inquisitor

New Member
Joined
Jan 15, 2012
Messages
15
Variations of this have been asked before, possibly the exact same thing but I'm still struggling to get this to work right -- please do feel free to point me to another post if this has been answered.

Given a spreadsheet with two (non-adjacent, though I don't think it matters) columns. Column A has names, or may be blank. Column B has an "X" in it, or may be blank.

I'd like a single number that counts the number of X marks for the UNIQUE set of names in Column A. Preferably without using a helper column because I'd like to drag the formula across another 11 columns of Xes.

Given this table:
A B
One X
Two X
Three
Four X
Two X
Three
One X
Four

The value I'd like at the bottom of the B column should be 3 (representing that One, Two, and Four have Xes).

Thanks for any help you can provide!
 
Sort by date Sort by votes

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Well here I am back again. Seems like this formula has been working great for a long time but now I've run into a new situation. So, my whole data layout actually is broken down by month. It looks something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Service[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]ONE[/TD]
[TD]A[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ONE[/TD]
[TD]B[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TWO[/TD]
[TD]A[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]THREE[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]FOUR[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]FOUR[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]ONE[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


I'm using this formula to count customers that only have the A service:
=SUM(IF(FREQUENCY(IF(Table75[Company]<>"",IF(ISNA(MATCH(Table75[Company],
IF(Table75[Service]="B",Table75[Company]),0)),IF(R$2:R$166>0,
MATCH("~"&Table75[Company],Table75[Company]&"",0)))),
ROW(Table75[Company])-ROW($C$2)+1),1))

"$C" corresponds to the Company column.
"R" corresponds to the 1 column.
The formula has "S$2:S$166" in the 2 column, of course.

So this has worked fine so far because Company ONE has always had both A and B services. But in May they transitioned only to having the A service. The formula isn't taking that into account and I honestly don't know where to start to address that issue. Of course I have a similar formula to count the customers that only have the B service. If I can figure out how to deal with one of these cases, I can figure out the other.

Thanks in advance for any help/guidance you can provide.
 
Upvote 0
inquisitor said:
Well here I am back again. Seems like this formula has been working great for a long time but now I've run into a new situation. So, my whole data layout actually is broken down by month. It looks something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Company
[/TD]
[TD]Service[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]ONE[/TD]
[TD]A[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ONE[/TD]
[TD]B[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TWO[/TD]
[TD]A[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]THREE[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]FOUR[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]FOUR[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]ONE[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


I'm using this formula to count customers that only have the A service:
=SUM(IF(FREQUENCY(IF(Table75[Company]<>"",IF(ISNA(MATCH(Table75[Company],
IF(Table75[Service]="B",Table75[Company]),0)),IF(R$2:R$166>0,
MATCH("~"&Table75[Company],Table75[Company]&"",0)))),
ROW(Table75[Company])-ROW($C$2)+1),1))

"$C" corresponds to the Company column.
"R" corresponds to the 1 column.
The formula has "S$2:S$166" in the 2 column, of course.

So this has worked fine so far because Company ONE has always had both A and B services. But in May they transitioned only to having the A service. The formula isn't taking that into account and I honestly don't know where to start to address that issue. Of course I have a similar formula to count the customers that only have the B service. If I can figure out how to deal with one of these cases, I can figure out the other.

Thanks in advance for any help/guidance you can provide.
Click to expand...

The formula you have mixes two different reference systems. And it is hard to map on the formula we had constructed in the previous posts. Anyway:

=SUM(IF(FREQUENCY(IF(Table75[Company]<>"",IF(ISNA(MATCH(Table75[Company],
IF(Table75[Service]="B",Table75[Company]),0)),IF(R$2:R$166>0,
MATCH("~"&Table75[Company],Table75[Company]&"",0)))),
ROW(Table75[Company])-ROW($C$2)+1),1))

means: Create a distinct company count of the companies which do not have B service and are associated with R-values > 0.

If you want a distinct company count of the companies with service = A and R-values > 0, control+shift+enter:

=SUM(IF(FREQUENCY(IF(Table75[Company]<>"",IF(Table75[Service]="A",IF(R$2:R$166>0,
MATCH("~"&Table75[Company],Table75[Company]&"",0)))),
ROW(Table75[Company])-ROW($C$2)+1),1))

would get that count. Does it do so?
 
Upvote 0
Aladin Akyurek said:
If you want a distinct company count of the companies with service = A and R-values > 0, control+shift+enter:

=SUM(IF(FREQUENCY(IF(Table75[Company]<>"",IF(Table75[Service]="A",IF(R$2:R$166>0,
MATCH("~"&Table75[Company],Table75[Company]&"",0)))),
ROW(Table75[Company])-ROW($C$2)+1),1))

would get that count. Does it do so?
Click to expand...


Not quite what I'm looking for I think, as this gives me a a very large value (38) instead of the small one I expect (5). Perhaps this is counting all entries where Company has A service at all? Versus what I'm looking for is being able to count those with ONLY A service. Compare that to those with ONLY B service. And finally, figure out how many have both. The desired results of the formula for the sample data are as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Both[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.
 
Upvote 0
inquisitor said:
Not quite what I'm looking for I think, as this gives me a a very large value (38) instead of the small one I expect (5). Perhaps this is counting all entries where Company has A service at all? Versus what I'm looking for is being able to count those with ONLY A service. Compare that to those with ONLY B service. And finally, figure out how many have both. The desired results of the formula for the sample data are as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Both[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.
Click to expand...
[TABLE="width: 672"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Company[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Service[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]ONE[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]ONE[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]TWO[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]THREE[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]FOUR[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]FOUR[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]ONE[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Both[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2[/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"]3[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]3[/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"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

B11, control+shift+enter and copy across:

=SUM(IF(FREQUENCY(IF($A$2:$A$8<>"",IF(ISNA(MATCH($A$2:$A$8,IF($B$2:$B$8="B",$A$2:$A$8),0)),IF(ISNUMBER(INDEX($C$2:$N$8,0,MATCH(B$10,$C$1:$N$1,0))),MATCH("~"&$A$2:$A$8,$A$2:$A$8&"",0)))),ROW($A$2:$A$8)-ROW($A$2)+1),1))

B12, control+shift+enter and copy across:

=SUM(IF(FREQUENCY(IF($A$2:$A$8<>"",IF(ISNA(MATCH($A$2:$A$8,IF($B$2:$B$8="A",$A$2:$A$8),0)),IF(ISNUMBER(INDEX($C$2:$N$8,0,MATCH(B$10,$C$1:$N$1,0))),MATCH("~"&$A$2:$A$8,$A$2:$A$8&"",0)))),ROW($A$2:$A$8)-ROW($A$2)+1),1))

B13, control+shift+enter and copy across:

=SUM(IF(FREQUENCY(IF($A$2:$A$8<>"",IF(ISNUMBER(INDEX($C$2:$N$8,0,MATCH(B$10,$C$1:$N$1,0))),MATCH("~"&$A$2:$A$8,$A$2:$A$8&"",0))),ROW($A$2:$A$8)-ROW($A$2)+1),1))
 
Last edited:
Upvote 0
I've reproduced the table you show above exactly in Excel and used the exact same formulas to ensure i have them right before transposing them onto my real data, but all the values in rows 11-13 are zero with those formulas.

Though, in the results above, I note that A stays 1 all the way across. Granted from Jan-Apr, customer TWO has service A, and A alone. Starting in May though, TWO continues to have service A and ONE falls back to having just service A as well so the A row should have four 1s and eight 2s.

I'm doing the shift-ctrl-enter thing, so not sure what I might have messed up here. Any ideas?
 
Upvote 0
inquisitor said:
I've reproduced the table you show above exactly in Excel and used the exact same formulas to ensure i have them right before transposing them onto my real data, but all the values in rows 11-13 are zero with those formulas.

Though, in the results above, I note that A stays 1 all the way across. Granted from Jan-Apr, customer TWO has service A, and A alone. Starting in May though, TWO continues to have service A and ONE falls back to having just service A as well so the A row should have four 1s and eight 2s.

I'm doing the shift-ctrl-enter thing, so not sure what I might have messed up here. Any ideas?
Click to expand...

Ok. Partially shown:

[TABLE="width: 144"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Company
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Service
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]ONE
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]100
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]ONE
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]100
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]TWO
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]100
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]THREE
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]FOUR
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]FOUR
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]ONE
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Given the above, why are Both = 1 and Total = 2, as can be surmised from the earlier posts?
 
Upvote 0
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.

Aladin Akyurek said:
Ok. Partially shown:

[TABLE="width: 144"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Company[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Service[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]ONE[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]ONE[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]TWO[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]THREE[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]FOUR[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]FOUR[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]ONE[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Given the above, why are Both = 1 and Total = 2, as can be surmised from the earlier posts?
Click to expand...
 
Upvote 0
inquisitor said:
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.
Click to expand...

[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().
 
Upvote 0
Outstanding. I ran into a bit of trouble because my cells actually have 0 values instead of being empty but was able to identify that as the root cause and update the formulas accordingly. Thank you again so much for your patience help -- invaluable.

Let's hope I don't need to add new services, though I have a disconcerting feeling that I will...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Rolled up color coding
Replies
3
Views
243
felixstraube
F
K
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
286
RobP
R
D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
653
felixstraube
F
B
  • Solved
How to return closest value based on two criteria?
Replies
6
Views
329
Peter_SSs
Peter_SSs
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
391
ChrisMac1
ChrisMac1

Forum statistics

Threads
1,223,992
Messages
6,175,827
Members
452,673
Latest member
LaMiaAvy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top