SOLVED
Home

Count unique values in a endless column

%3CLINGO-SUB%20id%3D%22lingo-sub-281520%22%20slang%3D%22en-US%22%3ECount%20unique%20values%20in%20a%20endless%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281520%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20stuck%20%3A)%3C%2FP%3E%3CP%3EI%20like%20to%20count%20the%20amount%20of%20unique%20values%20in%20a%20column.%20So%20far%20no%20problem%20with%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%7BSUMPRODUCT(1%2FCOUNTIF(A2%3AA2733%2CA2%3AA2733))%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20gives%20me%20the%20correct%20value.%3C%2FP%3E%3CP%3EBut%20the%20problem%20is%2C%20I%20make%20a%20report%2C%20and%20every%20week%20I%20need%20to%20update%20the%20report%20with%20new%20data.%20So%20today%20there%20are%202733%20rows%2C%20but%20tomorrow%20there%20will%20be%20more%20%3A)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20thought%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%7BSUMPRODUCT(1%2FCOUNTIF(A%3AA%2CA%3AA))%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERESULT%3A%20%23DIV%2F0!%3C%2FP%3E%3CP%3EWHY%3A%201%20divided%20with%20an%20empty%20cell%20%3D%201%20%2F%200%20%3D%20not%20possible%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20thought%2C%20lets%20build%20the%20formula%20up%20with%20a%20count%20of%20rows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB1%20%3DCOUNTA(A%3AA)%20%3D%202733%3C%2FP%3E%3CP%3E%3D%7BSUMPRODUCT(COUNTIF(INDIRECT(%22A1%3AA2733%22%26amp%3BB1)%2CINDIRECT(%22A1%3AA2733%22%26amp%3BB1)))%7D%3C%2FP%3E%3CP%3EBut%20again%3A%20RESULT%3A%20%23DIV%2F0!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20now%20I'm%20stuck!%20I%20like%20to%20count%20the%20unique%20values%20in%20a%20column%2C%20but%20not%20knowing%20up%20front%20how%20many%20rows%20there%20will%20be...%20can%20someone%20help%20me%20out%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EDennis%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-281520%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281605%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20values%20in%20a%20endless%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281605%22%20slang%3D%22en-US%22%3E%3CP%3EDennis%2C%20please%20check%20this%20article%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fcount-unique-numeric-values-with-criteria%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fcount-unique-numeric-values-with-criteria%3C%2FA%3E%20%26nbsp%3Bwhich%20explains%20how%20it%20works.%20It%20uses%20more%20compact%20variant%20of%20the%20formula%2C%20in%20your%20case%20it'll%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DSUM(--(FREQUENCY(IF((A2%3AA35000%26lt%3B%26gt%3B%22%22)*(B2%3AB35000%3D%22Active%22)%2CA2%3AA35000)%2CA2%3AA35000)%26gt%3B0))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281594%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20values%20in%20a%20endless%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281594%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20formula%2C...%20works%20like%20a%20charm!!!%3C%2FP%3E%3CP%3ECan%20you%20explain%20the%20formula%20a%20little%20bit%20more%20to%20me%20what%20it%20does%3F%20Because%20I%20need%20to%20at%20extra%20conditions%20to%20it%20(by%20example%20%3Dif(F%3AF%20%3D%20%22Active%22%2C%20SUM(...))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20i%20can%20only%20do%20that%20if%20I%20understand%20the%20formula%20better%2C%20because%20the%20formula%20below%20don't%20work%20%3A)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DIF(B%3AB%3D%22Active%22%2C%20SUM(IF(FREQUENCY(IF(A2%3AA35000%26lt%3B%26gt%3B%22%22%2CMATCH(A2%3AA35000%2CA2%3AA35000%2C0))%2CROW(A2%3AA35000)-ROW(%24A%242)%2B1)%2C1)))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281567%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20values%20in%20a%20endless%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281567%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20COUNTIF%20could%20be%20quite%20slow%20on%20big%20range.%20FREQUENCY%20shall%20be%20faster%3C%2FP%3E%0A%3CPRE%3E%3DSUM(IF(FREQUENCY(IF(A2%3AA35000%26lt%3B%26gt%3B%22%22%2CMATCH(A2%3AA35000%2CA2%3AA35000%2C0))%2CROW(A2%3AA35000)-ROW(%24A%242)%2B1)%2C1))%3C%2FPRE%3E%0A%3CP%3EAbove%20is%20array%20formula%20(Ctrl%2BShift%2BEnter)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281558%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20values%20in%20a%20endless%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281558%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20for%20your%20quick%20response!!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tested%20it%20on%20a%20sheet%20with%20only%2010%20rows%3A%20works%20perfect%3C%2FP%3E%3CP%3EI%20tested%20it%20on%20a%20sheet%20with%20the%202733%20rows%3A%20returns%200.%20When%20I%20saved%20the%20file%20he%20returns%20the%20correct%20value%20after%20a%20while%20(computer%20works%20slow%20for%20a%20minute%20of%202).%20But%20when%20I%20entered%20the%20cell%2C%20he%20returns%200%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20idea%20is%20that%20this%20is%20a%20very%20%22complex%22%20formula%20that%20need%20a%20lot%20of%20calculation%20power%20(compare%20every%20row%20with%20every%20row%20%3D%202733%5E2733%20%3D%207%2C469%2C289%20compares.%20I%20don't%20know%20how%20he%20does%20it%20with%20your%20formula%2C%20but%20I%20think%20he%20goes%20till%20the%20end%20of%20the%20workbook%20%3D%20a%20lot%20of%20rows%20%5E%20a%20lot%20of%20rows%20%3D%20to%20many%20rows%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20fact%20that%20I%20need%20that%20formula%20a%20few%20times%20in%20the%20same%20sheet%2C...%20this%20will%20be%20a%20disaster.%20Is%20there%20any%20other%20way%20to%20calculate%20this%20so%20he%20don't%20go%20till%20the%20end%20of%20the%20workbook%20until%20it's%20not%20necessary%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%2C%3C%2FP%3E%3CP%3EDennis%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281533%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20unique%20values%20in%20a%20endless%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281533%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Dennis%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBetter%20to%20use%20tables%2C%20with%20ranges%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((A2%3AA2733%26lt%3B%26gt%3B%22%22)%2FCOUNTIF(A2%3AA2733%2CA2%3AA2733%26amp%3B%22%22))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Dennis Depoorter
Occasional Contributor

Hi All,

 

I'm stuck :)

I like to count the amount of unique values in a column. So far no problem with the following formula:

 

={SUMPRODUCT(1/COUNTIF(A2:A2733,A2:A2733))}

 

This gives me the correct value.

But the problem is, I make a report, and every week I need to update the report with new data. So today there are 2733 rows, but tomorrow there will be more :)

 

So I thought:

 

={SUMPRODUCT(1/COUNTIF(A:A,A:A))}

 

RESULT: #DIV/0!

WHY: 1 divided with an empty cell = 1 / 0 = not possible

 

So I thought, lets build the formula up with a count of rows:

 

B1 =COUNTA(A:A) = 2733

={SUMPRODUCT(COUNTIF(INDIRECT("A1:A2733"&B1),INDIRECT("A1:A2733"&B1)))}

But again: RESULT: #DIV/0!

 

So now I'm stuck! I like to count the unique values in a column, but not knowing up front how many rows there will be... can someone help me out?

 

Thanks,

Dennis

 
 
 
 
 
 
 
 
5 Replies

Hi Dennis,

 

Better to use tables, with ranges formula could be

=SUMPRODUCT((A2:A2733<>"")/COUNTIF(A2:A2733,A2:A2733&""))

 

Thanks @Sergei Baklan for your quick response!!!!

 

I tested it on a sheet with only 10 rows: works perfect

I tested it on a sheet with the 2733 rows: returns 0. When I saved the file he returns the correct value after a while (computer works slow for a minute of 2). But when I entered the cell, he returns 0 again.

 

My idea is that this is a very "complex" formula that need a lot of calculation power (compare every row with every row = 2733^2733 = 7,469,289 compares. I don't know how he does it with your formula, but I think he goes till the end of the workbook = a lot of rows ^ a lot of rows = to many rows

 

The fact that I need that formula a few times in the same sheet,... this will be a disaster. Is there any other way to calculate this so he don't go till the end of the workbook until it's not necessary?

 

Best,

Dennis

Solution

Yes, COUNTIF could be quite slow on big range. FREQUENCY shall be faster

=SUM(IF(FREQUENCY(IF(A2:A35000<>"",MATCH(A2:A35000,A2:A35000,0)),ROW(A2:A35000)-ROW($A$2)+1),1))

Above is array formula (Ctrl+Shift+Enter)

 

Thanks for the formula,... works like a charm!!!

Can you explain the formula a little bit more to me what it does? Because I need to at extra conditions to it (by example =if(F:F = "Active", SUM(...))

 

But i can only do that if I understand the formula better, because the formula below don't work :)

 

=IF(B:B="Active", SUM(IF(FREQUENCY(IF(A2:A35000<>"",MATCH(A2:A35000,A2:A35000,0)),ROW(A2:A35000)-ROW($A$2)+1),1)))

 

 

 
 

Dennis, please check this article https://exceljet.net/formula/count-unique-numeric-values-with-criteria  which explains how it works. It uses more compact variant of the formula, in your case it'll be like

=SUM(--(FREQUENCY(IF((A2:A35000<>"")*(B2:B35000="Active"),A2:A35000),A2:A35000)>0))

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies