Forum Discussion
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!
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.
8 Replies
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.
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!
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.
