 SOLVED

# How to Count instances of a variable within another

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!

8 Replies
Best Response confirmed by ljoseph888 (New Contributor)
Solution

# Re: How to Count instances of a variable within another

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.

# Re: How to Count instances of a variable within another

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!

# Re: How to Count instances of a variable within another

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

# Re: How to Count instances of a variable within another

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.

# Re: How to Count instances of a variable within another

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!

# Re: How to Count instances of a variable within another

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.

# Re: How to Count instances of a variable within another

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

# Re: How to Count instances of a variable within another

@ljoseph888 , you are welcome