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