LinkBack URL
About LinkBacks
Hello
Hoping someone can help. I have the following formula
=COUNTIFS(Sheet1!$Y:$Y,Sheet1!$Q:$Q,G$6)
Column Y contains 100 of names but I only want to count the unique number. How can I change this formula?
Thanks in advance
Dan
Hello
Hoping someone can help. I have the following formula
=COUNTIFS(Sheet1!$Y:$Y,Sheet1!$Q:$Q,G$6)
Column Y contains 100 of names but I only want to count the unique number. How can I change this formula?
Thanks in advance
Dan
Hi,
This array formula should work for you:
=SUM(IF(FREQUENCY(IF(LEN(A:A)>0,MATCH(A:A,A:A,0),""), IF(LEN(A:A)>0,MATCH(A:A,A:A,0),""))>0,1))
Confirm with CTRL, SHIFT and ENTER.
Note that using whole columns is very inefficient and will slow down your sheet significantly. Consider using a dynamic named range to reduce resource gobbling!
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
attach a sample file
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Hi Sweep
Does this also incorporate the second part of my formula though where column Q needs to match G6?
Thanks
Sorry I misunderstood,
To confirm, you want to count how many unique entries are in column Y when column Q matches G6?but I only want to count the unique number
Sorry I misunderstood,
To confirm, you want to count how many unique entries are in column Y when column Q matches G6?but I only want to count the unique number
No probs Sweep.
Yes how many unique names appear in column Y when column Q matches G6
Thanks
OK,
try this array function:
=SUM(IF(FREQUENCY(IF($Q$1:$Q$24=$G$6,MATCH($Y$1:$Y$24,$Y$1:$Y$24,0)),ROW($Y$1:$Y$24)-ROW($Y$1)+1)>0,1))
Array Formula - Requires CTRL+SHIFT+ENTER
=SUM((Sheet1!$Q1:$Q10=G$6)*IFERROR(1/COUNTIF(Sheet1!$Y1:$Y10,Sheet1!$Y1:$Y10),0))
Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Sixth sense, can you show what the array would look like if I just need to count the unique names in a range (1 column)
Array Formula - Requires CTRL+SHIFT+ENTER
=SUM(IFERROR(1/COUNTIF(Q1:$Q10,Q1:$Q10),0))
Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks