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

Question

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).

Answer 1

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)

Answer 2

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

Answer 3

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.

1/ Insert a pivot table based while your columns of data are selected

2/ Below is what you will get after a) selecting which fields for rows, b) what to display in value cells (count(Gender)), c) proceeding to a few settings in fields (right-click on field in pivot table to get contextual menu).