# Count the number combinations present if the order doesn't matter

1. ## Count the number combinations present if the order doesn't matter

Count the number combinations present if the order doesn't matter (using pivot tables)
If I had column A and column B:

A B
red green
green red
red green
blue pink
pink blue
blue pink
blue pink
black white
black white
white black

Let's say I have hundreds of rows of combinations. What I need to do is on a second sheet, show all the different combinations and the number of times each occurs. So for the above, the result would be:

Combination: Number of times:
red green 3 (whether it is green first or red first doesn't matter and likewise for the others)
blue pink 4
black white 3

So, I would need it to give me the combination and the number of times it occurs. Any idea how I could do this? Ideally using pivot tables...

2. ## Re: Count the number combinations present if the order doesn't matter

Not sure it can be done with Pivot tables

You could put this into Column C copied down or something similar in a table with known combinations

=COUNTIFS(\$A\$1:\$A\$10,A1, \$B\$1:\$B\$10,B1)+COUNTIFS(\$A\$1:\$A\$10,B1, \$B\$1:\$B\$10,A1)

3. ## Re: Count the number combinations present if the order doesn't matter

Thanks, Chemist B.

My problem with this is I dont have a list of known combinations. I need to determine both the combinations I have and the number of times each occurs (again irrelevant of the order that they are in). Any further suggestions would be greatly appreciated.

BOY101

4. ## Re: Count the number combinations present if the order doesn't matter

Okay, I did this in two steps

In E2 copied down
=IF(AND(COUNTIF(\$E1:\$E\$1,A2)+COUNTIF(\$F1:\$F\$1,A2), COUNTIF(\$E1:\$E\$1,B2)+COUNTIF(\$F1:\$F\$1,B2)),"",A2)
In F2 copied down
=IF(AND(COUNTIF(\$E1:\$E\$1,B2)+COUNTIF(\$F1:\$F\$1,B2), COUNTIF(\$E1:\$E\$1,A2)+COUNTIF(\$F1:\$F\$1,A2)),"",B2)
E and F can be hidden once you create them.

In H2 as an ARRAY Function copied down
=IFERROR(INDEX(\$E\$2:\$E\$11, SMALL(IF(LEN(\$E\$2:\$E\$11)>0, ROW(\$E\$2:\$E\$11)-ROW(\$E\$2)+1),ROWS(\$A\$1:\$A1))),"")
I2
=IFERROR(INDEX(\$F\$2:\$F\$11, SMALL(IF(LEN(\$F\$2:\$F\$11)>0, ROW(\$F\$2:\$F\$11)-ROW(\$F\$2)+1),ROWS(\$A\$1:\$A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

and lastly in J2
=COUNTIFS(\$A\$2:\$A\$11,\$H2, \$B\$2:\$B\$11,\$I2)+COUNTIFS(\$A\$2:\$A\$11,\$I2, \$B\$2:\$B\$11,\$H2)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1