Counting only Unique cells
 

I have a column (within a database) containing hundreds of Project Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can appear
more than once in the column. I need to count the number of unique Project
Numbers within that column, based on the criteria of another column. Is
there a way to do that using a combination of the DCOUNTA function and a
formula? Any help would be greatly appreciated.
Thanks.

Counting only Unique cells
 

Start here
http://www.j-walk.com/ss/excel/tips/tip52.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message ...
I have a column (within a database) containing hundreds of Project Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can appear
more than once in the column. I need to count the number of unique Project
Numbers within that column, based on the criteria of another column. Is
there a way to do that using a combination of the DCOUNTA function and a
formula? Any help would be greatly appreciated.
Thanks.

Counting only Unique cells
 

Ron,
Thanks for your suggestion. Unfortunately, there was no example (or
solution) that counts unique cells (within a database) containing text at the
URL you provided.
Do you know of any other sites I can try?
Thanks again.
Bob


"Ron de Bruin" wrote:

Start here
http://www.j-walk.com/ss/excel/tips/tip52.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message ...
I have a column (within a database) containing hundreds of Project Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can appear
more than once in the column. I need to count the number of unique Project
Numbers within that column, based on the criteria of another column. Is
there a way to do that using a combination of the DCOUNTA function and a
formula? Any help would be greatly appreciated.
Thanks.

Counting only Unique cells
 

Hi

Try he
http://www.cpearson.com/excel/duplic...CountingUnique

Andy.

"Bob" wrote in message
...
Ron,
Thanks for your suggestion. Unfortunately, there was no example (or
solution) that counts unique cells (within a database) containing text at
the
URL you provided.
Do you know of any other sites I can try?
Thanks again.
Bob


"Ron de Bruin" wrote:

Start here
http://www.j-walk.com/ss/excel/tips/tip52.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message
...
I have a column (within a database) containing hundreds of Project
Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can
appear
more than once in the column. I need to count the number of unique
Project
Numbers within that column, based on the criteria of another column.
Is
there a way to do that using a combination of the DCOUNTA function and
a
formula? Any help would be greatly appreciated.
Thanks.

Counting only Unique cells
 

Try this array formula

Count unique items in data(text, values, Formulas)

=SUM(IF(LEN(data),1/COUNTIF(data,data)))


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message ...
Ron,
Thanks for your suggestion. Unfortunately, there was no example (or
solution) that counts unique cells (within a database) containing text at the
URL you provided.
Do you know of any other sites I can try?
Thanks again.
Bob


"Ron de Bruin" wrote:

Start here
http://www.j-walk.com/ss/excel/tips/tip52.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message ...
I have a column (within a database) containing hundreds of Project Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can appear
more than once in the column. I need to count the number of unique Project
Numbers within that column, based on the criteria of another column. Is
there a way to do that using a combination of the DCOUNTA function and a
formula? Any help would be greatly appreciated.
Thanks.

Counting only Unique cells
 

Andy,
Thanks for your suggestion. Unfortunately , there was no example (or
solution) that counts unique cells (within a DATABASE) containing text at the
URL you provided. I need to use the DCOUNTA function.
Do you know of any other sites I can try?
Thanks again.
Bob


"Andy" wrote:

Hi

Try he
http://www.cpearson.com/excel/duplic...CountingUnique

Andy.

"Bob" wrote in message
...
Ron,
Thanks for your suggestion. Unfortunately, there was no example (or
solution) that counts unique cells (within a database) containing text at
the
URL you provided.
Do you know of any other sites I can try?
Thanks again.
Bob


"Ron de Bruin" wrote:

Start here
http://www.j-walk.com/ss/excel/tips/tip52.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message
...
I have a column (within a database) containing hundreds of Project
Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can
appear
more than once in the column. I need to count the number of unique
Project
Numbers within that column, based on the criteria of another column.
Is
there a way to do that using a combination of the DCOUNTA function and
a
formula? Any help would be greatly appreciated.
Thanks.

Counting only Unique cells
 

Ron,
Thanks again, but I am looking for a solution that works in an Excel
"database", and specifically, in conjunction with the DCOUNTA function.
Bob


"Ron de Bruin" wrote:

Try this array formula

Count unique items in data(text, values, Formulas)

=SUM(IF(LEN(data),1/COUNTIF(data,data)))


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message ...
Ron,
Thanks for your suggestion. Unfortunately, there was no example (or
solution) that counts unique cells (within a database) containing text at the
URL you provided.
Do you know of any other sites I can try?
Thanks again.
Bob


"Ron de Bruin" wrote:

Start here
http://www.j-walk.com/ss/excel/tips/tip52.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message ...
I have a column (within a database) containing hundreds of Project Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can appear
more than once in the column. I need to count the number of unique Project
Numbers within that column, based on the criteria of another column. Is
there a way to do that using a combination of the DCOUNTA function and a
formula? Any help would be greatly appreciated.
Thanks.

Counting only Unique cells
 

Why would you need DCOUNTA?

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Bob" wrote in message
...
Andy,
Thanks for your suggestion. Unfortunately , there was no example (or
solution) that counts unique cells (within a DATABASE) containing text at
the
URL you provided. I need to use the DCOUNTA function.
Do you know of any other sites I can try?
Thanks again.
Bob


"Andy" wrote:

Hi

Try he
http://www.cpearson.com/excel/duplic...CountingUnique

Andy.

"Bob" wrote in message
...
Ron,
Thanks for your suggestion. Unfortunately, there was no example (or
solution) that counts unique cells (within a database) containing text
at
the
URL you provided.
Do you know of any other sites I can try?
Thanks again.
Bob


"Ron de Bruin" wrote:

Start here
http://www.j-walk.com/ss/excel/tips/tip52.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message
...
I have a column (within a database) containing hundreds of Project
Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number
can
appear
more than once in the column. I need to count the number of unique
Project
Numbers within that column, based on the criteria of another column.
Is
there a way to do that using a combination of the DCOUNTA function
and
a
formula? Any help would be greatly appreciated.
Thanks.

Counting only Unique cells
 

Because I want to count only certain Project Numbers based on the criteria
contained within another column. And the only way I know how to do this is
using a database function like DCOUNTA. If you know of a non-database method
that will accomplish the SAME result, I'm all ears.
Thanks.

"Peo Sjoblom" wrote:

Why would you need DCOUNTA?

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Bob" wrote in message
...
Andy,
Thanks for your suggestion. Unfortunately , there was no example (or
solution) that counts unique cells (within a DATABASE) containing text at
the
URL you provided. I need to use the DCOUNTA function.
Do you know of any other sites I can try?
Thanks again.
Bob


"Andy" wrote:

Hi

Try he
http://www.cpearson.com/excel/duplic...CountingUnique

Andy.

"Bob" wrote in message
...
Ron,
Thanks for your suggestion. Unfortunately, there was no example (or
solution) that counts unique cells (within a database) containing text
at
the
URL you provided.
Do you know of any other sites I can try?
Thanks again.
Bob


"Ron de Bruin" wrote:

Start here
http://www.j-walk.com/ss/excel/tips/tip52.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message
...
I have a column (within a database) containing hundreds of Project
Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number
can
appear
more than once in the column. I need to count the number of unique
Project
Numbers within that column, based on the criteria of another column.
Is
there a way to do that using a combination of the DCOUNTA function
and
a
formula? Any help would be greatly appreciated.
Thanks.

Counting only Unique cells
 

Assume the criteria range is B5:B500 and the range you want to count is
A5:A500 (adapt to fit your requirements)

=COUNT(1/FREQUENCY(IF(B$5:B$500=D2,MATCH(A$5:A$500,A$5:A$50 0,0)),ROW(A$5:A$500)-ROW(A$5)+1))

entered with ctrl + shift & enter will return the distinct count of values
in A5:A500 where B5:B500 (courtesy of Domenic)

another way would be to use advanced filter which is similar to the D
functions criteria wise, where you can use a formula like

=SUBTOTAL(3,$A$5:$A$500)

make sure there is a header in B4, put the header in D1 and the criteria in
D2

apply the filter on A4:B500 and the criteria range is $D$1:$D$2
select unique records only and click OK Now the subtotal formula will give
you the count

After you are done select show all from the datafilter menu

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Bob" wrote in message
...
Because I want to count only certain Project Numbers based on the criteria
contained within another column. And the only way I know how to do this
is
using a database function like DCOUNTA. If you know of a non-database
method
that will accomplish the SAME result, I'm all ears.
Thanks.

"Peo Sjoblom" wrote:

Why would you need DCOUNTA?

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Bob" wrote in message
...
Andy,
Thanks for your suggestion. Unfortunately , there was no example (or
solution) that counts unique cells (within a DATABASE) containing text
at
the
URL you provided. I need to use the DCOUNTA function.
Do you know of any other sites I can try?
Thanks again.
Bob


"Andy" wrote:

Hi

Try he
http://www.cpearson.com/excel/duplic...CountingUnique

Andy.

"Bob" wrote in message
...
Ron,
Thanks for your suggestion. Unfortunately, there was no example (or
solution) that counts unique cells (within a database) containing
text
at
the
URL you provided.
Do you know of any other sites I can try?
Thanks again.
Bob


"Ron de Bruin" wrote:

Start here
http://www.j-walk.com/ss/excel/tips/tip52.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message
...
I have a column (within a database) containing hundreds of Project
Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number
can
appear
more than once in the column. I need to count the number of
unique
Project
Numbers within that column, based on the criteria of another
column.
Is
there a way to do that using a combination of the DCOUNTA
function
and
a
formula? Any help would be greatly appreciated.
Thanks.

Counting only Unique cells
 

Peo,

Thanks!! I was hoping to avoid the use of the Advanced Filter since it is
an extra step I would need to remember every time the data is updated and the
query is re-run.

I will also try your first solution. Thanks again for all your help.

Bob

"Peo Sjoblom" wrote:

Assume the criteria range is B5:B500 and the range you want to count is
A5:A500 (adapt to fit your requirements)

=COUNT(1/FREQUENCY(IF(B$5:B$500=D2,MATCH(A$5:A$500,A$5:A$50 0,0)),ROW(A$5:A$500)-ROW(A$5)+1))

entered with ctrl + shift & enter will return the distinct count of values
in A5:A500 where B5:B500 (courtesy of Domenic)

another way would be to use advanced filter which is similar to the D
functions criteria wise, where you can use a formula like

=SUBTOTAL(3,$A$5:$A$500)

make sure there is a header in B4, put the header in D1 and the criteria in
D2

apply the filter on A4:B500 and the criteria range is $D$1:$D$2
select unique records only and click OK Now the subtotal formula will give
you the count

After you are done select show all from the datafilter menu

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Bob" wrote in message
...
Because I want to count only certain Project Numbers based on the criteria
contained within another column. And the only way I know how to do this
is
using a database function like DCOUNTA. If you know of a non-database
method
that will accomplish the SAME result, I'm all ears.
Thanks.

"Peo Sjoblom" wrote:

Why would you need DCOUNTA?

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Bob" wrote in message
...
Andy,
Thanks for your suggestion. Unfortunately , there was no example (or
solution) that counts unique cells (within a DATABASE) containing text
at
the
URL you provided. I need to use the DCOUNTA function.
Do you know of any other sites I can try?
Thanks again.
Bob


"Andy" wrote:

Hi

Try he
http://www.cpearson.com/excel/duplic...CountingUnique

Andy.

"Bob" wrote in message
...
Ron,
Thanks for your suggestion. Unfortunately, there was no example (or
solution) that counts unique cells (within a database) containing
text
at
the
URL you provided.
Do you know of any other sites I can try?
Thanks again.
Bob


"Ron de Bruin" wrote:

Start here
http://www.j-walk.com/ss/excel/tips/tip52.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message
...
I have a column (within a database) containing hundreds of Project
Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number
can
appear
more than once in the column. I need to count the number of
unique
Project
Numbers within that column, based on the criteria of another
column.
Is
there a way to do that using a combination of the DCOUNTA
function
and
a
formula? Any help would be greatly appreciated.
Thanks.

Counting only Unique cells
 

I forgot to mention that D2 holds the criteria in the first solution

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Bob" wrote in message
...
Peo,

Thanks!! I was hoping to avoid the use of the Advanced Filter since it is
an extra step I would need to remember every time the data is updated and
the
query is re-run.

I will also try your first solution. Thanks again for all your help.

Bob

"Peo Sjoblom" wrote:

Assume the criteria range is B5:B500 and the range you want to count is
A5:A500 (adapt to fit your requirements)

=COUNT(1/FREQUENCY(IF(B$5:B$500=D2,MATCH(A$5:A$500,A$5:A$50 0,0)),ROW(A$5:A$500)-ROW(A$5)+1))

entered with ctrl + shift & enter will return the distinct count of
values
in A5:A500 where B5:B500 (courtesy of Domenic)

another way would be to use advanced filter which is similar to the D
functions criteria wise, where you can use a formula like

=SUBTOTAL(3,$A$5:$A$500)

make sure there is a header in B4, put the header in D1 and the criteria
in
D2

apply the filter on A4:B500 and the criteria range is $D$1:$D$2
select unique records only and click OK Now the subtotal formula will
give
you the count

After you are done select show all from the datafilter menu

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Bob" wrote in message
...
Because I want to count only certain Project Numbers based on the
criteria
contained within another column. And the only way I know how to do
this
is
using a database function like DCOUNTA. If you know of a non-database
method
that will accomplish the SAME result, I'm all ears.
Thanks.

"Peo Sjoblom" wrote:

Why would you need DCOUNTA?

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Bob" wrote in message
...
Andy,
Thanks for your suggestion. Unfortunately , there was no example
(or
solution) that counts unique cells (within a DATABASE) containing
text
at
the
URL you provided. I need to use the DCOUNTA function.
Do you know of any other sites I can try?
Thanks again.
Bob


"Andy" wrote:

Hi

Try he
http://www.cpearson.com/excel/duplic...CountingUnique

Andy.

"Bob" wrote in message
...
Ron,
Thanks for your suggestion. Unfortunately, there was no example
(or
solution) that counts unique cells (within a database) containing
text
at
the
URL you provided.
Do you know of any other sites I can try?
Thanks again.
Bob


"Ron de Bruin" wrote:

Start here
http://www.j-walk.com/ss/excel/tips/tip52.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" wrote in message
...
I have a column (within a database) containing hundreds of
Project
Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project
Number
can
appear
more than once in the column. I need to count the number of
unique
Project
Numbers within that column, based on the criteria of another
column.
Is
there a way to do that using a combination of the DCOUNTA
function
and
a
formula? Any help would be greatly appreciated.
Thanks.