I have a column in a list that contains types of expenses. I want to count
how many different types of expenses I have. In other words, if one of the
expense types is "office" and there are several instances of "office", I just
want to count "office" once. If "fees" is in the column several times, I just
want to count fees once and so on.

The way I do it now is to use the Subtotal feature to group the expenses,
and then click on the level 2 button. The level 2 subtotals will return just
one instance of each type. I then copy them and use paste special to paste
the values in cells off to the side. I can then use the count function to
count each type of expense.

So I am wondering if there is a better way or if there is a function that
will count just one instance of a group within a field.

What you want to do is count the unique items. Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"ibvalentine" wrote in message
...
I have a column in a list that contains types of expenses. I want to count
how many different types of expenses I have. In other words, if one of the
expense types is "office" and there are several instances of "office", I
just
want to count "office" once. If "fees" is in the column several times, I
just
want to count fees once and so on.

The way I do it now is to use the Subtotal feature to group the expenses,
and then click on the level 2 button. The level 2 subtotals will return
just
one instance of each type. I then copy them and use paste special to paste
the values in cells off to the side. I can then use the count function to
count each type of expense.

So I am wondering if there is a better way or if there is a function that
will count just one instance of a group within a field.

Thanks, Biff. It works, but I have no idea why. I will have to take a good
look at it tomorrow. Can you give me a brief explanation of the formula?

"T. Valko" wrote:

What you want to do is count the unique items. Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"ibvalentine" wrote in message
...
I have a column in a list that contains types of expenses. I want to count
how many different types of expenses I have. In other words, if one of the
expense types is "office" and there are several instances of "office", I
just
want to count "office" once. If "fees" is in the column several times, I
just
want to count fees once and so on.

The way I do it now is to use the Subtotal feature to group the expenses,
and then click on the level 2 button. The level 2 subtotals will return
just
one instance of each type. I then copy them and use paste special to paste
the values in cells off to the side. I can then use the count function to
count each type of expense.

So I am wondering if there is a better way or if there is a function that
will count just one instance of a group within a field.

Let's start with a list that is being counted in A1:A10.

Bob,John,Bob,Bob,John,John,Bob,Bill,Bill,Max

The basic formula to count unique items is

=SUMPRODUCT(1/COUNTIF($A$1:$A$10,$A$1:$A$10))

The COUNTIF($A$1:$A$10,$A$1:$A$10) part of the formula builds an array of
the number of occurrences of each item, in this case{4;3;4;4;3;3;4;2;2;1}.
As can be seen, each occurrence of the repeated value is counted, so there
are four occurrences of Bob in the array. There will always be the same
number of occurrences of value as the count of that value, unless two or
more items are repeated the same number of times, in which case it will be
some multiple of that count.

Thus the item that is repeated 4 times has 4 instances of that count,
dividing 1 by the count of 4, gives 0.25 4 times. The full array of values
is

{0.25;0.333333333333333;0.25;0.25;0.33333333333333 3;0.333333333333333;0.25;0
..5;0.5;1}.

The item that repeats 4 times sums to 1. The item that repeats 3 times also
sums to 1. It should be clear from this that every value works in the same
way and sums to 1. In other words, 1 is returned for every unique item. The
sum of these values becomes the count of unique items.

As our test range is A1:A20, and some of the items in A1:A20 are blank,
extending this formula to A1:A20 would return a #DIV/0! Error.

The reason for the error is blank cells in the full range A1:A20. Each blank
cell returns a 0 value from the COUNTIF formula, which gives the #DIV/0!
Error when divided into 1.

The solution to this is to force it to count the empty cells as well, and
not return a zero. Adding &"" to the end of the COUNTIF formula forces a
count of the blanks.

This addition on its own removes the #DIV/0! error, but will cause the
blanks to be counted as a unique item. A further addition to the formula
resolves this by testing for those blanks. Instead of dividing the array of
counts into 1 each time, adding the test creates an array of TRUE/FALSE
values to be divided by the equivalent element in the counts array. Each
blank will resolve to FALSE in the dividend array, and the count of the
blanks in the divisor array. The result of this will be 0, so the blanks do
not get counted.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ibvalentine" wrote in message
...
Thanks, Biff. It works, but I have no idea why. I will have to take a good
look at it tomorrow. Can you give me a brief explanation of the formula?

"T. Valko" wrote:

What you want to do is count the unique items. Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"ibvalentine" wrote in message
...
I have a column in a list that contains types of expenses. I want to
count
how many different types of expenses I have. In other words, if one of
the
expense types is "office" and there are several instances of "office",
I
just
want to count "office" once. If "fees" is in the column several times,
I
just
want to count fees once and so on.

The way I do it now is to use the Subtotal feature to group the
expenses,
and then click on the level 2 button. The level 2 subtotals will return
just
one instance of each type. I then copy them and use paste special to
paste
the values in cells off to the side. I can then use the count function
to
count each type of expense.

So I am wondering if there is a better way or if there is a function
that
will count just one instance of a group within a field.

Bob,

Thank you for taking the time to explain this to me. Your explanation was
clear and concise, and you have saved me a lot of time. I really appreciate
both you guys for imparting your expertize!

"Bob Phillips" wrote:

Let's start with a list that is being counted in A1:A10.

Bob,John,Bob,Bob,John,John,Bob,Bill,Bill,Max

The basic formula to count unique items is

=SUMPRODUCT(1/COUNTIF($A$1:$A$10,$A$1:$A$10))

The COUNTIF($A$1:$A$10,$A$1:$A$10) part of the formula builds an array of
the number of occurrences of each item, in this case{4;3;4;4;3;3;4;2;2;1}.
As can be seen, each occurrence of the repeated value is counted, so there
are four occurrences of Bob in the array. There will always be the same
number of occurrences of value as the count of that value, unless two or
more items are repeated the same number of times, in which case it will be
some multiple of that count.

Thus the item that is repeated 4 times has 4 instances of that count,
dividing 1 by the count of 4, gives 0.25 4 times. The full array of values
is

{0.25;0.333333333333333;0.25;0.25;0.33333333333333 3;0.333333333333333;0.25;0
..5;0.5;1}.

The item that repeats 4 times sums to 1. The item that repeats 3 times also
sums to 1. It should be clear from this that every value works in the same
way and sums to 1. In other words, 1 is returned for every unique item. The
sum of these values becomes the count of unique items.

As our test range is A1:A20, and some of the items in A1:A20 are blank,
extending this formula to A1:A20 would return a #DIV/0! Error.

The reason for the error is blank cells in the full range A1:A20. Each blank
cell returns a 0 value from the COUNTIF formula, which gives the #DIV/0!
Error when divided into 1.

The solution to this is to force it to count the empty cells as well, and
not return a zero. Adding &"" to the end of the COUNTIF formula forces a
count of the blanks.

This addition on its own removes the #DIV/0! error, but will cause the
blanks to be counted as a unique item. A further addition to the formula
resolves this by testing for those blanks. Instead of dividing the array of
counts into 1 each time, adding the test creates an array of TRUE/FALSE
values to be divided by the equivalent element in the counts array. Each
blank will resolve to FALSE in the dividend array, and the count of the
blanks in the divisor array. The result of this will be 0, so the blanks do
not get counted.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ibvalentine" wrote in message
...
Thanks, Biff. It works, but I have no idea why. I will have to take a good
look at it tomorrow. Can you give me a brief explanation of the formula?

"T. Valko" wrote:

What you want to do is count the unique items. Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"ibvalentine" wrote in message
...
I have a column in a list that contains types of expenses. I want to
count
how many different types of expenses I have. In other words, if one of
the
expense types is "office" and there are several instances of "office",
I
just
want to count "office" once. If "fees" is in the column several times,
I
just
want to count fees once and so on.

The way I do it now is to use the Subtotal feature to group the
expenses,
and then click on the level 2 button. The level 2 subtotals will return
just
one instance of each type. I then copy them and use paste special to
paste
the values in cells off to the side. I can then use the count function
to
count each type of expense.

So I am wondering if there is a better way or if there is a function
that
will count just one instance of a group within a field.

Excellent explanation, Bob!

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
Let's start with a list that is being counted in A1:A10.

Bob,John,Bob,Bob,John,John,Bob,Bill,Bill,Max

The basic formula to count unique items is

=SUMPRODUCT(1/COUNTIF($A$1:$A$10,$A$1:$A$10))

The COUNTIF($A$1:$A$10,$A$1:$A$10) part of the formula builds an array of
the number of occurrences of each item, in this case{4;3;4;4;3;3;4;2;2;1}.
As can be seen, each occurrence of the repeated value is counted, so there
are four occurrences of Bob in the array. There will always be the same
number of occurrences of value as the count of that value, unless two or
more items are repeated the same number of times, in which case it will be
some multiple of that count.

Thus the item that is repeated 4 times has 4 instances of that count,
dividing 1 by the count of 4, gives 0.25 4 times. The full array of values
is

{0.25;0.333333333333333;0.25;0.25;0.33333333333333 3;0.333333333333333;0.25;0
.5;0.5;1}.

The item that repeats 4 times sums to 1. The item that repeats 3 times
also
sums to 1. It should be clear from this that every value works in the same
way and sums to 1. In other words, 1 is returned for every unique item.
The
sum of these values becomes the count of unique items.

As our test range is A1:A20, and some of the items in A1:A20 are blank,
extending this formula to A1:A20 would return a #DIV/0! Error.

The reason for the error is blank cells in the full range A1:A20. Each
blank
cell returns a 0 value from the COUNTIF formula, which gives the #DIV/0!
Error when divided into 1.

The solution to this is to force it to count the empty cells as well, and
not return a zero. Adding &"" to the end of the COUNTIF formula forces a
count of the blanks.

This addition on its own removes the #DIV/0! error, but will cause the
blanks to be counted as a unique item. A further addition to the formula
resolves this by testing for those blanks. Instead of dividing the array
of
counts into 1 each time, adding the test creates an array of TRUE/FALSE
values to be divided by the equivalent element in the counts array. Each
blank will resolve to FALSE in the dividend array, and the count of the
blanks in the divisor array. The result of this will be 0, so the blanks
do
not get counted.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"ibvalentine" wrote in message
...
Thanks, Biff. It works, but I have no idea why. I will have to take a
good
look at it tomorrow. Can you give me a brief explanation of the formula?

"T. Valko" wrote:

What you want to do is count the unique items. Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"ibvalentine" wrote in message
...
I have a column in a list that contains types of expenses. I want to
count
how many different types of expenses I have. In other words, if one of
the
expense types is "office" and there are several instances of "office",
I
just
want to count "office" once. If "fees" is in the column several times,
I
just
want to count fees once and so on.

The way I do it now is to use the Subtotal feature to group the
expenses,
and then click on the level 2 button. The level 2 subtotals will
return
just
one instance of each type. I then copy them and use paste special to
paste
the values in cells off to the side. I can then use the count function
to
count each type of expense.

So I am wondering if there is a better way or if there is a function
that
will count just one instance of a group within a field.

Thank you Biff, appreciated.

"T. Valko" wrote in message
...
Excellent explanation, Bob!

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
Let's start with a list that is being counted in A1:A10.

Bob,John,Bob,Bob,John,John,Bob,Bill,Bill,Max

The basic formula to count unique items is

=SUMPRODUCT(1/COUNTIF($A$1:$A$10,$A$1:$A$10))

The COUNTIF($A$1:$A$10,$A$1:$A$10) part of the formula builds an array of
the number of occurrences of each item, in this
case{4;3;4;4;3;3;4;2;2;1}.
As can be seen, each occurrence of the repeated value is counted, so
there
are four occurrences of Bob in the array. There will always be the same
number of occurrences of value as the count of that value, unless two or
more items are repeated the same number of times, in which case it will
be
some multiple of that count.

Thus the item that is repeated 4 times has 4 instances of that count,
dividing 1 by the count of 4, gives 0.25 4 times. The full array of
values
is

{0.25;0.333333333333333;0.25;0.25;0.33333333333333 3;0.333333333333333;0.25;0
.5;0.5;1}.

The item that repeats 4 times sums to 1. The item that repeats 3 times
also
sums to 1. It should be clear from this that every value works in the
same
way and sums to 1. In other words, 1 is returned for every unique item.
The
sum of these values becomes the count of unique items.

As our test range is A1:A20, and some of the items in A1:A20 are blank,
extending this formula to A1:A20 would return a #DIV/0! Error.

The reason for the error is blank cells in the full range A1:A20. Each
blank
cell returns a 0 value from the COUNTIF formula, which gives the #DIV/0!
Error when divided into 1.

The solution to this is to force it to count the empty cells as well, and
not return a zero. Adding &"" to the end of the COUNTIF formula forces a
count of the blanks.

This addition on its own removes the #DIV/0! error, but will cause the
blanks to be counted as a unique item. A further addition to the formula
resolves this by testing for those blanks. Instead of dividing the array
of
counts into 1 each time, adding the test creates an array of TRUE/FALSE
values to be divided by the equivalent element in the counts array. Each
blank will resolve to FALSE in the dividend array, and the count of the
blanks in the divisor array. The result of this will be 0, so the blanks
do
not get counted.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"ibvalentine" wrote in message
...
Thanks, Biff. It works, but I have no idea why. I will have to take a
good
look at it tomorrow. Can you give me a brief explanation of the formula?

"T. Valko" wrote:

What you want to do is count the unique items. Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"ibvalentine" wrote in message
...
I have a column in a list that contains types of expenses. I want to
count
how many different types of expenses I have. In other words, if one
of the
expense types is "office" and there are several instances of
"office", I
just
want to count "office" once. If "fees" is in the column several
times, I
just
want to count fees once and so on.

The way I do it now is to use the Subtotal feature to group the
expenses,
and then click on the level 2 button. The level 2 subtotals will
return
just
one instance of each type. I then copy them and use paste special to
paste
the values in cells off to the side. I can then use the count
function to
count each type of expense.

So I am wondering if there is a better way or if there is a function
that
will count just one instance of a group within a field.