Count depending on instance?

David Kemp

David Kemp

New Member
Joined
Sep 29, 2013
Messages
20
Office Version
  1. 365
Platform
  1. Windows
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Tony
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Tony
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Frank
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Frank
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Frank
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Tony
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Tony
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Kate
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Kate
[/TD]
[TD]No
[/TD]
[/TR]
</tbody>[/TABLE]

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?
 
Sort by date Sort by votes
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

ABC
NameY/N
TonyYes
TonyYes
FrankNo
FrankYes
FrankYes
TonyNo
TonyYes
KateNo
KateNo
Count of Y/NY/N
NameNoYes
Frank
Kate
Tony

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:81px;"><col style="width:46px;"><col style="width:30px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0
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

ABC

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: left"]Tony[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: right"]0.333333[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]Tony[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: right"]0.333333[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]Frank[/TD]
[TD="align: left"]No[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]Frank[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: right"]0.5[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]Frank[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: right"]0.5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]Tony[/TD]
[TD="align: left"]No[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]Tony[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: right"]0.333333[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]Kate[/TD]
[TD="align: left"]No[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: left"]Kate[/TD]
[TD="align: left"]No[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]2[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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.
 
Upvote 0
David Kemp said:
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Tony[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

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?
Click to expand...

Control+shift+enter, not just enter:
Rich (BB code): 
=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))
 
Upvote 0
Solution
Hi Aladin, I was sure it was possible but I certainly couldn't manage it in a single Array. I can only say :pray:.

One more to study.

Troy.
 
Upvote 0
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

 
Upvote 0
my 2 cents

Sheet1

*ABCDEFGH
NameY/NUnique NamesYesNo
TonyYesTony
TonyYesFrank
FrankNoKate
FrankYes
FrankYes
TonyNo
TonyYes
KateNo
KateNo

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
@dispelthemyth

Frequency for unique count is faster than Hager's SUM/COUNTIF idiom. Also, I believe a global distinct count is asked for...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Formula Calculation is Inadvertently Switching from Automatic to Manual...
Replies
0
Views
390
MNexcelguy19
M
R
  • Solved
VBA question regarding multiple instances of strings
Replies
8
Views
388
Peter_SSs
Peter_SSs
B
  • Solved
Table lookup and returning an answer based on a specific condition
Replies
5
Views
581
Peter_SSs
Peter_SSs
franklin_m
  • Question Question
VBA Search row for instances of pattern in two adjacent cells
Replies
3
Views
496
franklin_m
franklin_m
R
  • Question Question
Excel Database and Dependant Lists
Replies
7
Views
365
felixstraube
F

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