Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Unique Count sensitive to hidden/filtered rows
Hello.
I need to count unique values in a list. I have many ways to do this (I am currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0, 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine. But - I wish to be able to filter the list and have the function adjust to only the visible cells. I tried substituting the SUBTOTAL function for SUM in the formula above, but I get an ERROR result. I am willing to use intermediate columns if I must. Can anyone help? |
#2
|
|||
|
|||
One way albeit rather complicated
http://tinyurl.com/9rfmv Regards, Peo Sjoblom "Ian" wrote: Hello. I need to count unique values in a list. I have many ways to do this (I am currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0, 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine. But - I wish to be able to filter the list and have the function adjust to only the visible cells. I tried substituting the SUBTOTAL function for SUM in the formula above, but I get an ERROR result. I am willing to use intermediate columns if I must. Can anyone help? |
#3
|
|||
|
|||
Thanks for the reply, Peo. I tried using the following function (the one you
pointed to with my range substituted): =SUM(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(ROW(A2:A100)),,1)),(MMULT(((A2:A100=TRANSPOSE( A2:A100)*TRANSPOSE(SUBTOTAL(3,OFFSET(A2,ROW(A2:A10 0)-MIN(ROW(A2:A100)),,1))))*(ROW(A2:A100)=TRANSPOSE( ROW(A2:A100)))),ROW(A2:A100)*0+1) =1)*1)) It looks like that requires numeric entries (MMULT function)? I get a #VALUE error. I have text entries in the cells I am trying to count. Any other ideas, anyone, or can you help me understand what I did wrong translating the idea provided by Peo? -- Ian "Peo Sjoblom" wrote: One way albeit rather complicated http://tinyurl.com/9rfmv Regards, Peo Sjoblom "Ian" wrote: Hello. I need to count unique values in a list. I have many ways to do this (I am currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0, 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine. But - I wish to be able to filter the list and have the function adjust to only the visible cells. I tried substituting the SUBTOTAL function for SUM in the formula above, but I get an ERROR result. I am willing to use intermediate columns if I must. Can anyone help? |
#4
|
|||
|
|||
Thanks for the reply, Peo. Unfortunately, I must be doing something wrong. I
get a #VALUE error. Doest the formula you referenced require numeric values only? I see that it uses the MMULT function. Thanks again!!! -- Ian "Peo Sjoblom" wrote: One way albeit rather complicated http://tinyurl.com/9rfmv Regards, Peo Sjoblom "Ian" wrote: Hello. I need to count unique values in a list. I have many ways to do this (I am currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0, 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine. But - I wish to be able to filter the list and have the function adjust to only the visible cells. I tried substituting the SUBTOTAL function for SUM in the formula above, but I get an ERROR result. I am willing to use intermediate columns if I must. Can anyone help? |
#5
|
|||
|
|||
Try...
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9:A1000,RO W(A9:A1000)-MIN(ROW(A9: A1000)),0,1)),MATCH(A9:A1000,A9:A1000,0)),IF(SUBTO TAL(3,OFFSET(A9:A1000,R OW(A9:A1000)-MIN(ROW(A9:A1000)),0,1)),MATCH(A9:A1000,A9:A1000,0 )))0,1,0) ) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Ian wrote: Hello. I need to count unique values in a list. I have many ways to do this (I am currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0, 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine. But - I wish to be able to filter the list and have the function adjust to only the visible cells. I tried substituting the SUBTOTAL function for SUM in the formula above, but I get an ERROR result. I am willing to use intermediate columns if I must. Can anyone help? |
#6
|
|||
|
|||
Yes it does, but if you have let's say names in A2:A300 and use filter and
want to count unique names in the filtered list you can add a help column and include it in the filter, i.e. in the help column put =COUNTIF($A$2:A2,A2)=1 copy down as long as needed, now include the help column in the filter and filter on TRUE, then just count the TRUE with =SUBTOTAL(3,E2:E300) where E2:E300 would be the help column with the countif formulas will give you # of unique names -- Regards, Peo Sjoblom "Ian" wrote in message ... Thanks for the reply, Peo. Unfortunately, I must be doing something wrong. I get a #VALUE error. Doest the formula you referenced require numeric values only? I see that it uses the MMULT function. Thanks again!!! -- Ian "Peo Sjoblom" wrote: One way albeit rather complicated http://tinyurl.com/9rfmv Regards, Peo Sjoblom "Ian" wrote: Hello. I need to count unique values in a list. I have many ways to do this (I am currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0, 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine. But - I wish to be able to filter the list and have the function adjust to only the visible cells. I tried substituting the SUBTOTAL function for SUM in the formula above, but I get an ERROR result. I am willing to use intermediate columns if I must. Can anyone help? |
#7
|
|||
|
|||
What range is being filtered, and on what column and value?
Alan Beban Ian wrote: Thanks for the reply, Peo. Unfortunately, I must be doing something wrong. I get a #VALUE error. Doest the formula you referenced require numeric values only? I see that it uses the MMULT function. Thanks again!!! -- Ian "Peo Sjoblom" wrote: One way albeit rather complicated http://tinyurl.com/9rfmv Regards, Peo Sjoblom "Ian" wrote: Hello. I need to count unique values in a list. I have many ways to do this (I am currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0, 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine. But - I wish to be able to filter the list and have the function adjust to only the visible cells. I tried substituting the SUBTOTAL function for SUM in the formula above, but I get an ERROR result. I am willing to use intermediate columns if I must. Can anyone help? |
#8
|
|||
|
|||
This worked perfectly! Thanks very much, everyone for you wonderful help.
-- Ian "Domenic" wrote: Try... =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9:A1000,RO W(A9:A1000)-MIN(ROW(A9: A1000)),0,1)),MATCH(A9:A1000,A9:A1000,0)),IF(SUBTO TAL(3,OFFSET(A9:A1000,R OW(A9:A1000)-MIN(ROW(A9:A1000)),0,1)),MATCH(A9:A1000,A9:A1000,0 )))0,1,0) ) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Ian wrote: Hello. I need to count unique values in a list. I have many ways to do this (I am currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0, 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine. But - I wish to be able to filter the list and have the function adjust to only the visible cells. I tried substituting the SUBTOTAL function for SUM in the formula above, but I get an ERROR result. I am willing to use intermediate columns if I must. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique in Pivot Table | Excel Discussion (Misc queries) | |||
Count Rows with Conditional Format? | Excel Discussion (Misc queries) | |||
Pivot Table Unique Count | Excel Worksheet Functions | |||
How do I count or display unique data in a column? | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |