Counting and summing based on 2 columns

[inquisitor]

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!

 

[Aladin Akyurek]

Outstanding! Thanks so much.

You are welcome. Thanks for providing feedback.

 

[inquisitor]

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:

Company	Service	1	2	3	4	5	6	7	8	9	10	11	12
ONE	A	100	100	100	100
ONE	B	100	100	100	100
TWO	A	100	100	100	100	100	100	100	100	100	100	100	100
THREE	B					100	100	100	100	100	100	100	100
FOUR	A									100	100	100	100
FOUR	B									100	100	100	100
ONE	A					100	100	100	100	100	100	100	100

<tbody>
</tbody>

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.

 

[Aladin Akyurek]

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:

Company	Service	1	2	3	4	5	6	7	8	9	10	11	12
ONE	A	100	100	100	100
ONE	B	100	100	100	100
TWO	A	100	100	100	100	100	100	100	100	100	100	100	100
THREE	B					100	100	100	100	100	100	100	100
FOUR	A									100	100	100	100
FOUR	B									100	100	100	100
ONE	A					100	100	100	100	100	100	100	100

<tbody>
</tbody>

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.

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?

 

[inquisitor]

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?

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:

A	1	1	1	1	2	2	2	2	2	2	2	2
B	0	0	0	0	1	1	1	1	1	1	1	1
Both	1	1	1	1	0	0	0	0	1	1	1	1
Total	2	2	2	2	3	3	3	3	4	4	4	4

<tbody>
</tbody>

Thanks in advance.

 

[Aladin Akyurek]

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:

A	1	1	1	1	2	2	2	2	2	2	2	2
B					1	1	1	1	1	1	1	1
Both	1	1	1	1					1	1	1	1
Total	2	2	2	2	3	3	3	3	4	4	4	4

<tbody>
</tbody>

Thanks in advance.

Company	Service	1	2	3	4	5	6	7	8	9	10	11	12
ONE	A	100	100	100	100
ONE	B	100	100	100	100
TWO	A	100	100	100	100	100	100	100	100	100	100	100	100
THREE	B					100	100	100	100	100	100	100	100
FOUR	A									100	100	100	100
FOUR	B									100	100	100	100
ONE	A					100	100	100	100	100	100	100	100
	1	2	3	4	5	6	7	8	9	10	11	12
A	1	1	1	1	1	1	1	1	1	1	1	1
B	0	0	0	0	1	1	1	1	1	1	1	1
Both	2	2	2	2	3	3	3	3	4	4	4	4

<tbody>
</tbody>

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))

 

[inquisitor]

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?

 

[Aladin Akyurek]

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?

Ok. Partially shown:

Company	Service	1
ONE	A	100
ONE	B	100
TWO	A	100
THREE	B
FOUR	A
FOUR	B
ONE	A

<tbody>
</tbody>

Given the above, why are Both = 1 and Total = 2, as can be surmised from the earlier posts?

 

[inquisitor]

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.

Ok. Partially shown:

Company	Service	1
ONE	A	100
ONE	B	100
TWO	A	100
THREE	B
FOUR	A
FOUR	B
ONE	A

<tbody>
</tbody>

Given the above, why are Both = 1 and Total = 2, as can be surmised from the earlier posts?

 

[Aladin Akyurek]

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.

Company	Service	1	2	3	4	5	6	7	8	9	10	11	12
ONE	A	100	100	100	100
ONE	B	100	100	100	100
TWO	A	100	100	100	100	100	100	100	100	100	100	100	100
THREE	B					100	100	100	100	100	100	100	100
FOUR	A									100	100	100	100
FOUR	B									100	100	100	100
ONE	A					100	100	100	100	100	100	100	100
		1	2	3	4	5	6	7	8	9	10	11	12
A		1	1	1	1	2	2	2	2	2	2	2	2
B		0	0	0	0	1	1	1	1	1	1	1	1
Both		1	1	1	1	0	0	0	0	1	1	1	1
Total		2	2	2	2	3	3	3	3	4	4	4	4

<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>
</tbody>

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().

 

[inquisitor]

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...