Count depending on instance?

[David Kemp]

Tony	Yes
Tony	Yes
Frank	No
Frank	Yes
Frank	Yes
Tony	No
Tony	Yes
Kate	No
Kate	No

<tbody>
</tbody>

Hi everyone,

I want to count how many times yes has occurred once for each person. For example, Tony has 3 yes's, Frank has 2, Kate none.

I want another cell to count them and return a value of 2, one for all of Tony's yes's, and one for all of Frank's.

This is just a basic mock up, the real deal will have hundreds of names. Any suggestions?

 

[Teeroy]

This is ideal for a Pivot Table. The output below (in A16) is a Pivot Table with Names as Row, Y/N as Column, and Count Y/N as Data.

Sheet1

	A	B	C
1	Name	Y/N
2	Tony	Yes
3	Tony	Yes
4	Frank	No
5	Frank	Yes
6	Frank	Yes
7	Tony	No
8	Tony	Yes
9	Kate	No
10	Kate	No
11
12
13
14
15
16	Count of Y/N	Y/N
17	Name	No	Yes
18	Frank	1	2
19	Kate	2
20	Tony	1	3

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:81px;"><col style="width:46px;"><col style="width:30px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Teeroy]

I forgot to add, to get the data you wanted from the pivot Table (which gives more analysis than requested) use

=COUNT(C18:C20)

You can do it directly with an array formula but I've had too many computer issues today to have the brainpower left to formulate it. Maybe if you don'e get an another answer by tomorrow...

 

[David Kemp]

Thanks Teeroy.

Unfortunately, Pivot table won't be possible as it is part of a larger case management database that gets updated every day by many people. Every cell has drop down menus and the like, with other pages linked to these cells.

These are just two columns off one page, each page has about 30 columns, each with various categories of data.

Any other way of doing it whilst maintaining the format?

 

[Teeroy]

Hi David,

I haven't been able to think of a way to do this as a single Array Formula. I can only think of 2 options;

1. you could have a Pivot Table on another sheet and manage the data Range by VBA code; or
2. you could use a helper column.

An example using a helper column (column C) is given below:

Sheet1

	A	B	C
1	Tony	Yes	0.333333
2	Tony	Yes	0.333333
3	Frank	No	0
4	Frank	Yes	0.5
5	Frank	Yes	0.5
6	Tony	No	0
7	Tony	Yes	0.333333
8	Kate	No	0
9	Kate	No	0
10			2

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C1 =IF(SUMPRODUCT(--(A$1:A$9=A1),--(B$1:B$9="Yes"))=0,0,1/SUMPRODUCT(--(A$1:A$9=A1),--(B$1:B$9="Yes"))*(B$1:B$9="Yes")) C2 =IF(SUMPRODUCT(--(A$1:A$9=A2),--(B$1:B$9="Yes"))=0,0,1/SUMPRODUCT(--(A$1:A$9=A2),--(B$1:B$9="Yes"))*(B$1:B$9="Yes")) C3 =IF(SUMPRODUCT(--(A$1:A$9=A3),--(B$1:B$9="Yes"))=0,0,1/SUMPRODUCT(--(A$1:A$9=A3),--(B$1:B$9="Yes"))*(B$1:B$9="Yes")) C4 =IF(SUMPRODUCT(--(A$1:A$9=A4),--(B$1:B$9="Yes"))=0,0,1/SUMPRODUCT(--(A$1:A$9=A4),--(B$1:B$9="Yes"))*(B$1:B$9="Yes")) C5 =IF(SUMPRODUCT(--(A$1:A$9=A5),--(B$1:B$9="Yes"))=0,0,1/SUMPRODUCT(--(A$1:A$9=A5),--(B$1:B$9="Yes"))*(B$1:B$9="Yes")) C6 =IF(SUMPRODUCT(--(A$1:A$9=A6),--(B$1:B$9="Yes"))=0,0,1/SUMPRODUCT(--(A$1:A$9=A6),--(B$1:B$9="Yes"))*(B$1:B$9="Yes")) C7 =IF(SUMPRODUCT(--(A$1:A$9=A7),--(B$1:B$9="Yes"))=0,0,1/SUMPRODUCT(--(A$1:A$9=A7),--(B$1:B$9="Yes"))*(B$1:B$9="Yes")) C8 =IF(SUMPRODUCT(--(A$1:A$9=A8),--(B$1:B$9="Yes"))=0,0,1/SUMPRODUCT(--(A$1:A$9=A8),--(B$1:B$9="Yes"))*(B$1:B$9="Yes")) C9 =IF(SUMPRODUCT(--(A$1:A$9=A9),--(B$1:B$9="Yes"))=0,0,1/SUMPRODUCT(--(A$1:A$9=A9),--(B$1:B$9="Yes"))*(B$1:B$9="Yes")) C10 =SUM(C1:C9) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Just fill in C1 and copy the formula down to C9. This helper column can be hidden if desired.

When you Sum everything in the helper column it gives the number you are after.

I hope this helps. I'm reasonably sure an Array Formula solution should be possible but you'll you need someone better at them than I am (Aladin Akyurek comes to mind, he is fantastic at Array Formulas).

Troy.

 

[Aladin Akyurek]

Tony	Yes
Tony	Yes
Frank	No
Frank	Yes
Frank	Yes
Tony	No
Tony	Yes
Kate	No
Kate	No

<tbody>
</tbody>

Hi everyone,

I want to count how many times yes has occurred once for each person. For example, Tony has 3 yes's, Frank has 2, Kate none.

I want another cell to count them and return a value of 2, one for all of Tony's yes's, and one for all of Frank's.

This is just a basic mock up, the real deal will have hundreds of names. Any suggestions?

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$10<>"",IF($B$2:$B$10="Yes",
  MATCH("~"&$A$2:$A$10,$A$2:$A$10&"",0))),ROW($A$2:$A$10)-ROW($A$2)+1),1))

 

[Teeroy]

Hi Aladin, I was sure it was possible but I certainly couldn't manage it in a single Array. I can only say ray:.

One more to study.

Troy.

 

[ITWare2008]

Hi David Kemp,

I achieved your results using this formula

SUMPRODUCT(--($AN$114:$AN$122=AP114),--($AO$114:$AO$122="Yes"))

whereas AP114 stores the name for a particular person.

Nevertheless I'm feeling a little bit, to say the least, stupid after reading the response from "master" Akyurek.

Anyway I hope this helps.

Vândalo

 

[dispelthemyth]

my 2 cents

Sheet1

*	A	B	C	D	E	F	G	H
1	Name	Y/N		Unique Names	3		Yes	No
2	Tony	Yes				Tony	3	1
3	Tony	Yes				Frank	2	1
4	Frank	No				Kate	0	2
5	Frank	Yes
6	Frank	Yes
7	Tony	No
8	Tony	Yes
9	Kate	No
10	Kate	No
11

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:99px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E1 {=SUM(1/COUNTIF(A2:A10,A2:A10))} F2 {=IF((ROW()-1)>$E$1,"",INDEX($A$2:$A$20,MATCH(0,COUNTIF($F$1:F1,$A$2:$A$20),0)))} G2 =COUNTIFS($A$2:$A$20, $F2, $B$2:$B$20, G$1) H2 =COUNTIFS($A$2:$A$20, $F2, $B$2:$B$20, H$1) F3 {=IF((ROW()-1)>$E$1,"",INDEX($A$2:$A$20,MATCH(0,COUNTIF($F$1:F2,$A$2:$A$20),0)))} G3 =COUNTIFS($A$2:$A$20, $F3, $B$2:$B$20, G$1) H3 =COUNTIFS($A$2:$A$20, $F3, $B$2:$B$20, H$1) F4 {=IF((ROW()-1)>$E$1,"",INDEX($A$2:$A$20,MATCH(0,COUNTIF($F$1:F3,$A$2:$A$20),0)))} G4 =COUNTIFS($A$2:$A$20, $F4, $B$2:$B$20, G$1) H4 =COUNTIFS($A$2:$A$20, $F4, $B$2:$B$20, H$1) F5 {=IF((ROW()-1)>$E$1,"",INDEX($A$2:$A$20,MATCH(0,COUNTIF($F$1:F4,$A$2:$A$20),0)))} F6 {=IF((ROW()-1)>$E$1,"",INDEX($A$2:$A$20,MATCH(0,COUNTIF($F$1:F5,$A$2:$A$20),0)))} F7 {=IF((ROW()-1)>$E$1,"",INDEX($A$2:$A$20,MATCH(0,COUNTIF($F$1:F6,$A$2:$A$20),0)))} F8 {=IF((ROW()-1)>$E$1,"",INDEX($A$2:$A$20,MATCH(0,COUNTIF($F$1:F7,$A$2:$A$20),0)))} F9 {=IF((ROW()-1)>$E$1,"",INDEX($A$2:$A$20,MATCH(0,COUNTIF($F$1:F8,$A$2:$A$20),0)))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

@dispelthemyth

Frequency for unique count is faster than Hager's SUM/COUNTIF idiom. Also, I believe a global distinct count is asked for...