google-sheets

Count number of pairs, triplets, etc in a google spreadsheet

If I have a list like below in a google spreadsheet, how could I count how many pairs, triplets, etc are in the list? The pair count would only count pairs and not triplets or higher groups.

apple
orange
pear
orange
apple
pear
orange

Result would give 2 for pair count and 1 for triplet count.

I would like to accomplish this with a formula in a single cell without using other cells for intermediate steps. Here is a link to a sheet showing the end result I want but using intermediate steps, which are unwanted.

https://docs.google.com/spreadsheets/d/1yVFYH226cebOu-adt-e_2VQnULdxWZKOWOnB4fK2OCc/edit?usp=sharing

Solution

  • I have found this to be the most compact answer:

    =ArrayFormula(SUM(--(COUNTIF(A1:A7;A1:A7)=2))/2) #for doubles
=ArrayFormula(SUM(--(COUNTIF(A1:A7;A1:A7)=3))/3) #for triples

    Dropping the "ArrayFormula" component to use in excel.