L Lois Mar 10, 2004 #1 How can i use the count function in Excel 2000 to count the number of cell that contain text but not including duplicates
How can i use the count function in Excel 2000 to count the number of cell that contain text but not including duplicates
B Bernie Deitrick Mar 10, 2004 #2 Lois, This Array formula, entered with Ctrl-Shift-Enter, will count the number of unique text strings in range A1:A1000: =INT(SUM(1/IF(ISTEXT(A1:A1000),COUNTIF(A1:A1000,A1:A1000),100000))) Update all three A1:A1000's to reflect your actual range. HTH, Bernie MS Excel MVP
Lois, This Array formula, entered with Ctrl-Shift-Enter, will count the number of unique text strings in range A1:A1000: =INT(SUM(1/IF(ISTEXT(A1:A1000),COUNTIF(A1:A1000,A1:A1000),100000))) Update all three A1:A1000's to reflect your actual range. HTH, Bernie MS Excel MVP
F Frank Kabel Mar 10, 2004 #3 Hi try =SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100&"")) Note: will count also blank rows as one entry. So if you have blanks rows in between you may subtract '1' from this formula
Hi try =SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100&"")) Note: will count also blank rows as one entry. So if you have blanks rows in between you may subtract '1' from this formula
