Mar 07 2020 09:46 AM - edited Mar 07 2020 09:48 AM
Hi, I'm trying to create a formula which counts the unique number of country names in the second sheet of column S attached. I want the range to be the entire column since we're continuously adding data to this sheet. Cell S1 and all blank cells should be ignored. Can anyone help?
Mar 07 2020 10:13 AM
Mar 07 2020 10:17 AM
Thank you for your reply! I'm looking for a solution which won't require me to copy any values in column S. More data will be added to the column as time goes by, so the idea is to create a formula which will automatically update the unique text count (number of country names) in case a new country name happens to be added to the column. Is there a formula that would accomplish this?
Mar 07 2020 10:22 AM
Mar 07 2020 11:55 AM
I presume you don't have 365 and access to UNIQUE...
=SUM(IF(FREQUENCY(IFNA(MATCH($S$2:$S$1000,$S$2:$S$1000,0),0),IFNA(MATCH($S$2:$S$1000,$S$2:$S$1000,0),0))>0,1))-1
Press Ctrl Shift Enter to calculate the formula as an array.
Mar 07 2020 11:55 AM
@peeblescd2020 =SUMPRODUCT(IFERROR(1/COUNTIF(S2:S1000,S2:S1000),0))