Counting independent items in a list
in [Functions]
|
From: Brad E. on 15 Mar 2010 14:28 This formula is meant to count independent entries in A1:A20. =ROUND(SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20)),0) Can anyone make this better? I am not talking about using Absolute references, but ways to modify it so it works in every case. For instance, at first I had an empty cell in my range and the #DIV/0 error was returned. I don't fully understand the two negatives in front of some formulas, but I am wondering if that would work better, too? =ROUND(SUMPRODUCT(1/--COUNTIF(A1:A20,A1:A20)),0) Thanks for any help. -- Brad E.
From: Mike H on 15 Mar 2010 15:31 Hi, here's an alternative that ignores blanks =SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A10,A1:A20&"")) the double unary (--) coerce true or false into one or zero but do nothing in your formula. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Brad E." wrote: > This formula is meant to count independent entries in A1:A20. > =ROUND(SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20)),0) > > Can anyone make this better? I am not talking about using Absolute > references, but ways to modify it so it works in every case. For instance, > at first I had an empty cell in my range and the #DIV/0 error was returned. > I don't fully understand the two negatives in front of some formulas, but I > am wondering if that would work better, too? > =ROUND(SUMPRODUCT(1/--COUNTIF(A1:A20,A1:A20)),0) > > Thanks for any help. > -- Brad E.
From: Mike H on 15 Mar 2010 15:36 Hi, I should have added there are other (better) ways to do this and Bernd P has a well presented web page on how it should be done http://www.sulprobil.com/html/count_unique.html -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Brad E." wrote: > This formula is meant to count independent entries in A1:A20. > =ROUND(SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20)),0) > > Can anyone make this better? I am not talking about using Absolute > references, but ways to modify it so it works in every case. For instance, > at first I had an empty cell in my range and the #DIV/0 error was returned. > I don't fully understand the two negatives in front of some formulas, but I > am wondering if that would work better, too? > =ROUND(SUMPRODUCT(1/--COUNTIF(A1:A20,A1:A20)),0) > > Thanks for any help. > -- Brad E.
From: Mike H on 15 Mar 2010 15:39 This is Bernd's webpage that i was looking for where he specifically comments on the solution I gave you http://www.sulprobil.com/html/excel_don_ts.html -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Brad E." wrote: > This formula is meant to count independent entries in A1:A20. > =ROUND(SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20)),0) > > Can anyone make this better? I am not talking about using Absolute > references, but ways to modify it so it works in every case. For instance, > at first I had an empty cell in my range and the #DIV/0 error was returned. > I don't fully understand the two negatives in front of some formulas, but I > am wondering if that would work better, too? > =ROUND(SUMPRODUCT(1/--COUNTIF(A1:A20,A1:A20)),0) > > Thanks for any help. > -- Brad E.
|
Pages: 1 Prev: Dynamically Adding Textbox To Frame Next: how to sum cells and ignore the #div/0! 's ? |