How to Count instances of a variable within another

[ljoseph888]

Complicated to explain . . . but I am trying to take a data set that looks something like this, with varying number of entries per person:

1.    person 1              A

2.    person 1              A

3.    person 1              B

4.    person 2              A

5.    person 2              B

6.   person 3               A

7.  person 3                A

8.  person 3                A

9.  person 3                A

And collapse it (or make a new data set) that has one line per person, with the number of As (and/or Bs)

                                #As

1.   Person 1              2

2.   Person 2              1

3.   Person 3:             4

Can Excel do this?  If so, can someone advise?

Thanks!

[#2 · Accepted solution]

@ljoseph888 

For such sample

One variant:

In E2

=UNIQUE($B$2:$B$10)

in F2

=IFERROR(AGGREGATE(14,6,1/($B$2:$B$10=E2)*COUNTIFS($B$2:$B$10,$B$2:$B$10,$C$2:$C$10,$C$2:$C$10),1),"")

and drag it down.

Another variant - Power Query as in attached file.

[#3]

@Sergei Baklan 

Follow up question (sorry, but you were so helpful!)

In my summary, I would like to include additional secondary variables.

For example:

                         var a          Count

1.  Person 1        March.       2

2.  Person 2.       March        1

3.  Person 3       April            4

Trying to figure it out on my own . . . 

Thanks!

[#4]

Try using countifs function.
=Countifs(criteria1 range,criteria1,criteria2 range,criteria2,.....)
P/s :
1. Criteria range will be the column where your data table for the variable located
2. Criteria will be the variable u looking for

[#5]

@ljoseph888 

Perhaps you may give bit more details. It will be like

Person1 March 5

Person1 April 4

Person1 May 6

Person2 June 2

I mean we may generate the list of persons and additionally the list of months for each person (all of them are different). Or list of months is predefined and we just count zero if nothing for the concrete person in some months.

[#6]

@Sergei Baklan 

Hi Sergei, 

Thanks!

I'm looking to pick up another variable in the output that is uniquely associated with the person.  So each person has another:

Input:

Output:

Actually, there are a few variables unique to each person I'd like to pick up in the output.

Also, I'm trying to decode the AGGREGATE function you originally sent, so that I actually understand it and can modify and apply it in other situations (want to learn to fish rather than just eat the fish, although eating the fish was very helpful :-)).

Here is the AGGREGATE command you wrote:  (I understand the IFERROR)

AGGREGATE(14,6,1/($B$2:$B$10=E2)*COUNTIFS($B$2:$B$10,$B$2:$B$10,$C$2:$C$10,$C$2:$C$10),1)

A few questions:

I understand that the four parameters passed to AGGREGATE are:

14 (LARGE function) (why large vs count?)

6 (ignore errors)

1/($B$2: . . . . . $C$10). --- array over which to aggregate

1 -- I am assuming this is the second parameter of the large function, to return the largest value?

I am really confused on the third line.  I don't understand the 1/ (is that a division sign?).  I figure the ($B$2:$B$10=E2) is aggregating unique entries in column B and putting the output starting at E2.  Not sure what function * has, I am assuming aggregate each unique entry in B by countifs, but I am confused that there is no criterion in the arguments for the countifs?  How does it know to count the #As.

Sorry to be a bother, but any light you can shed on this would be helpful.

Thanks!

[#7]

@ljoseph888 

If I understand your sample correctly, combination of the person name and Var A is always the same. Other words, if name is Sue when Var A is always z, it can't be another value, i.e. x.

If so

in O3 you may generate the list of such combinations by

=UNIQUE($K$3:$L$10)

And I'm not sure you'd like to count A, or B, or return the largest of above.

If count A for each person the simple COUNTIFS

=COUNTIFS($K$3:$K$10,UNIQUE($K$3:$K$10),$M$3:$M$10,"A")

and the same to count Bs. If largest of above, we use AGGREGATE

=IFERROR(
 AGGREGATE(14,6,
    1/($K$3:$K$10=O3)*
       COUNTIFS($K$3:$K$10,$K$3:$K$10,
                $M$3:$M$10,$M$3:$M$10)
  ,1),
"")

AGGREGATE returns first (4th parameter = 1) largest (1st parameter = 14) from the array (3rd parameter) ignoring all errors (2nd parameter = 6).

In the array the 1/($K$3:$K$10=O3) is the filter. Second part returns TRUE or FALSE for each name of the range if it is equal or not to the value in O3. Since in arithmetic operations TRUE and FALSE are equivalent of 1 and 0, dividing 1 on them we have an array of 1:s or errors depends on name is match or not.

This array we multiply on an array returned by COUNTIFS() which counts number of each record person/Var A in the source. Multiplying on filter we have such array only for the person in O3 and finally take largest value from that array.

[#8]

@Sergei Baklan 

Thank you, again, Sergei!  This is very helpful.  

[#9]

@ljoseph888 , you are welcome