# Count unique values across 3 columns

1. ## Count unique values across 3 columns

Suppose I have the following:

Col A Col B Col C Result
1/7/2015 M 1 0
1/7/2015 M 2 0
2/7/2015 A 1 1
2/7/2015 A 1 1
3/7/2015 C 2 0

I wish to count the number of occurances where col A and Col B and Col C are the same (this would appear in the result column.)

I have used countifs(A\$2:A\$100,A2,B\$2:B100,B2,C\$2:C100,C2) as array (ctrl+shift+enter) but I have 100,000+ rows and the copy and calculation is extremely slow.

Does anyone know of a faster way?

2. ## Re: Count unique values across 3 columns

Hi, welcome to the forum

That does not need to be ARRAY entered, it is a regular excel function, just enter it the normal way.

Not sure what else you added to that fu7nction, but I don't get the same results you do...
 A B C D E 1 A B C Result 2 1/7/2015 M 1 0 1 3 1/7/2015 M 2 0 1 4 2/7/2015 A 1 1 2 5 2/7/2015 A 1 1 2 6 3/7/2015 C 2 0 1

E2=COUNTIFS(\$A\$2:\$A\$100,A2,\$B\$2:\$B\$100,B2,\$C\$2:\$C\$100,C2)

Note, you had some of the absoluting (\$) a bit wrong, as well

3. ## Re: Count unique values across 3 columns

Thank you so much

I did not know it could be entered as a regular formula.

Is there a faster way that the same result can be achieved? When I copy the forumula down 100,000+ rows, it takes a VERY long time. I have searched everywhere but cannot seem to come up with a solution.

4. ## Re: Count unique values across 3 columns

The calcs should already be a lot quicker, plus, now that is not an ARRAY, it will not need to recalc with every WB change, it will only update when something directly changes it.

Im curious as to why you need to copy that down for every row though, seems a bit redundant?

Perhaps a Pivot Table, or a table with formulas showing just the unique values would suite you better?

5. ## Re: Count unique values across 3 columns

Originally Posted by FDibbins
now that is not an ARRAY, it will not need to recalc with every WB change, it will only update when something directly changes it.
You must be thinking of VOLATILE function formulas that recalc with every WB change. Array formulas do not do that unless they contain volatile functions.

6. ## Re: Count unique values across 3 columns

Well I have 100,000+ rows and approx. 50+ columns.

I am looking to identify the like values (those that return 2 above in the formula) and delete the other rows.

Is there a faster and/or better way this can be achieved?

I could do a pivot table, but then I need to go back and delete the rows I don't need?

7. ## Re: Count unique values across 3 columns

Originally Posted by Tony Valko
You must be thinking of VOLATILE function formulas that recalc with every WB change. Array formulas do not do that unless they contain volatile functions.
Good catch Tony, thanks. It was a bit late, last night.

Large ARRAY's will still slow things down though

8. ## Re: Count unique values across 3 columns

That's what it sounds like here.

When you have "big data" sometimes you just have to accept that things can take some time to complete.

Sounds like they have 100k formulas each referencing 100k rows so that's gonna take some time!

9. ## Re: Count unique values across 3 columns

Surely the result can be achieved quicker in another way or with a different formula?

10. ## Re: Count unique values across 3 columns

I have sometimes found that, if I am dealing with historic data - where the answers to formulas will no longer change - it may help to "value" those formulas down a feww '000 rows.

But, if you are dealing with a really big data base, then maybe you need to consider something like access, instead of excel

I could do a pivot table, but then I need to go back and delete the rows I don't need?
Not sure what you mean by that. PT's are seperate tables that will interogate a data base for you, and can be substantially faster than formulas

11. ## Re: Count unique values across 3 columns

I have sometimes found that, if I am dealing with historic data - where the answers to formulas will no longer change - it may help to "value" those formulas down a feww '000 rows.
Unfortunately the values will always change depending on parameters so I cannot "value" down the formulas.

Not sure what you mean by that. PT's are seperate tables that will interogate a data base for you, and can be substantially faster than formulas
Well you mentioned this earlier

Perhaps a Pivot Table, or a table with formulas showing just the unique values would suite you better?
But then I would not be able to delete the rows I don't need would I? The pivot table would show me the unqiue values and where they appear, but that would not achieve the goal of deleting irrelevant data??

12. ## Re: Count unique values across 3 columns

aahh ok, you did not mention that you wanted to delete rows. Is it time consuming because you need to find the duplicates, or because the calc is taking so long?

13. ## Re: Count unique values across 3 columns

ok thanks no problem. I thought I had,

I am looking to identify the like values (those that return 2 above in the formula) and delete the other rows.
I believe it's time consuming as the calc is taking so long but could be finding duplicates as well.

I have the data sorted by col A then by col B then by col C, so when A and B are the same, the countifs looks for col C to be the same also. I want to keep those rows and delete the others. But the countifs formula down the rows (which will always change, sometimes I could have less) takes a very long time.

So just looking for some alternative options from others to achieve this result??

14. ## Re: Count unique values across 3 columns

Can you post a SMALL sample file and show us what results you expect?

20 rows and a few columns worth of data is all we need.

15. ## Re: Count unique values across 3 columns

I must have missed that part, sorry

For the deleting, have you considered using filters?

Apply filters, filter on anything that is not 1, and then delete what shows

16. ## Re: Count unique values across 3 columns

I must have missed that part, sorry
That's ok

For the deleting, have you considered using filters?
Yes I have considered this, however I haven't been able to get to that yet as the formula takes so long

Can you post a SMALL sample file and show us what results you expect?
Sure, I have attached it.

You can see in the example, I would insert the countifs formula in col J, then I would copy it down.

I would then use VBA to delete the rows I don't need, in this case I would only keep rows 20, 21 and 29, 30.

17. ## Re: Count unique values across 3 columns

This may be "faster"...

Enter this formula in J17:

=A17&" "&B17&" "&C17

Enter this formula in K17:

=COUNTIF(J\$17:J\$35,J17)

Select J17:K17 and copy down as needed.

I guess you want to delete the rows where column K=1.

18. ## Re: Count unique values across 3 columns

This may be "faster"...

Enter this formula in J17:

=A17&" "&B17&" "&C17

Enter this formula in K17:

=COUNTIF(J\$17:J\$35,J17)

Select J17:K17 and copy down as needed.

I guess you want to delete the rows where column K=1.
Thanks but unfortunately it was pretty much the same as the original method. Still takes at least 2 mins to calculate

19. ## Re: Count unique values across 3 columns

Well, I'm out of suggestions.

2 mins isn't all that bad. I run files that take 20 mins to calculate (but they're doing some heavy duty regression analysis for xray calibration).

20. ## Re: Count unique values across 3 columns

Well, I'm out of suggestions.
ok thanks anyway. I am looking for something much quicker but if not then so be it.

21. ## Re: Count unique values across 3 columns

Maybe post this in the Excel Programming / VBA / Macros sub-forum. Someone might be able to come up with a macro that'll do this.

Good luck!

22. ## Re: Count unique values across 3 columns

Maybe post this in the Excel Programming / VBA / Macros sub-forum. Someone might be able to come up with a macro that'll do this.

Good luck!
ok thanks so much! Is there a way I can move this post or do I just submit the question again in the other sub-forum?

23. ## Re: Count unique values across 3 columns

I would just submit the question again and mention that you have posted this thread and include a link to this thread so folks can see what has already been discussed.

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