How to create number of occurrences in an excel out of a combination of values in columns-CodePudding

I probably named this question wrong, but I hope you'll understand (I can correct it if you suggest a better name).

My document has about 6 rows of data and 3 columns of parameters. I want to transfer these 6 rows into a smaller amount of rows that would represent different combination of parameters (columns). For example:

Gender | Card | Churned
Male | Visa | Yes
Male | Visa | No
Male | Visa | Yes
Female | Mastercard | Yes
Male | Mastercard | Yes
Female | Mastercard | Yes

... to be turned into this:

Gender | Card | Churned | Occurrences
Male | Visa | Yes | 2
Male | Visa | No | 1
Female | Mastercard | Yes | 2
Male | Mastercard | Yes | 1

Would you kindly provide advice on how to do this? I'd like to use the same guidelines for data with thousands of rows and tens of columns for example (big data).

CodePudding user response：

With Microsoft 365 you may use below formulas.

E2 =UNIQUE(A2:C7)
H2 =COUNTIFS($A$2:$A$7,E2,$B$2:$B$7,F2,$C$2:$C$7,G2)

CodePudding user response：

If you're in Excel 365, the function =UNIQUE() will output unique rows from a range (works on a multi-column range), and you can then use =COUNTIFS next to that to get the number of occurences.

Relevant syntax and usage info here and here

CodePudding user response：

Excel pivot tables reports have been designed for such purpose. Select the 3 columns of your table then select "Insert" > "Pivot table report" from the menu ; specify to create it in a new sheet. In the 'fields' control, drag & drop your 3 columns "gender" "card" and "churned". In the "sigma values" drag & drop any column (e.g. 'Gender') so that it is listed as 'Count(Gender)'.

After a few tuning of the report you should have what you need.