Count Distinct Values

[Johnny]

I have a table with several columns and a varying number of rows of data.

Each row represents work performed by an employee. One employee generally
has several rows of data: base on the amout of work performed.

Column one is the "Office" in which the employee works: we have 8 offices.
Column 2 is the employee's "Name". Column 3 is the employee type (e.g.
sales, secreatary, etc). Each column contains duplicate entries.

I need a formual which will provide me with the number of Sales Employees
for each office.

Thank you,

 

[Rich]

I have a table with several columns and a varying number of rows of data.

Each row represents work performed by an employee. One employee generally
has several rows of data: base on the amout of work performed.

Column one is the "Office" in which the employee works: we have 8 offices.
Column 2 is the employee's "Name". Column 3 is the employee type (e.g.
sales, secreatary, etc). Each column contains duplicate entries.

I need a formual which will provide me with the number of Sales Employees
for each office.

Thank you,

COUNTIF(C:C,C2) will give you number of employees of the type in the cell
c2. I do something very similar and copy the above in all the cells in
column D

 

[Pete_UK]

Suppose you list your offices elsewhere on the sheet (eg in X2:X9),
and put Sales in Y1, then in Y2 you could use this:

=SUMPRODUCT(($A$1:$A$1000=$X2)*($C$1:$C$1000=Y$1))

and copy down to Y9. I've assumed that you have 1000 rows of data -
adjust if you have more. You could put Secretary in Z1 and copy the
formula across to that column to get a count of secretaries at each
office. You could put your summary table onto another sheet, in which
case you will need to put the sheet name where your data is located in
front of the range references.

You say that an employee can have several rows (i.e. working in
different departments). If you have a column which contains the FTE or
proportion of time spent in each department (eg column F), then you
could modify the formula to this:

=SUMPRODUCT(($A$1:$A$1000=$X2)*($C$1:$C$1000=Y$1),$F$1:$F$1000)

to get the full-time equivalents.

Hope this helps.

Pete

 

[Rich]

COUNTIF(C:C,C2) will give you number of employees of the type in the
cell c2. I do something very similar and copy the above in all the cells
in column D

Just realised you wanted the number for each office. SUMPRODUCT will do that
well, see Pete_UK's answer.

 

[Johnny]

I did a little more digging through related posts and came up with this
formula that worked:

=SUMPRODUCT(($B$2:$B$417<>"")/COUNTIF('$B$2:$B$417,$B$2:$B$417&"")*(C$2:$C$417="R")*($A$2:$A$417=A3))

A3 is a reference to one of the 8 cities.

However, it doesn't work if there is less than 417 rows of data. Is there a
way I can keep one formula without having to change 417 to the actual number
of rows that contain data?

 

[Harlan Grove]

I did a little more digging through related posts and came up with this
formula that worked:

=SUMPRODUCT(($B$2:$B$417<>"")/COUNTIF('$B$2:$B$417,$B$2:$B$417&"")*(C$2:$C$417="R")*($A$2:$A$417=A3))

....

The single quote in this formula is a syntax error. Typo? Or does your
actual formula refer to ranges in another worksheet, which seems to be
the case since A3 would be within A2:A417 in your last criterion.

However, it doesn't work if there is less than 417 rows of data.  Is there a
way I can keep one formula without having to change 417 to the actual number
of rows that contain data?

The formula above may APPEAR to work with 416 rows of actual data, but
the COUNTIF denominator is an conditional count only on col B. If your
data in A2:C5 were

X 1 R
X 1 Q
X 1 R
X 2 R

and the A3 criterion value were X, your formula even adjusted to using
just rows 2 through 5 would return 1.6667 rather than 2 because the
COUNTIF call would return {3;3;3;1} rather than {2;2;2;1}. You need to
use something like the array formula

=SUM(($A$2:$A$417=A3)*($C$2:$C$417="R")/
MMULT(--($B$2:$B$417=TRANSPOSE($B$2:$B$417)),($A$2:$A$417=A3)*($C$2:$C
$417="R")))

or as a general template

=SUM(criteria_expression/
MMULT(--(value_range=TRANSPOSE(value_range)),criteria_expression))

will count the distinct values in value_range where
criteria_expression is satisfied.

 

[Harlan Grove]

=SUM(($A$2:$A$417=A3)*($C$2:$C$417="R")/
MMULT(--($B$2:$B$417=TRANSPOSE($B$2:$B$417)),
($A$2:$A$417=A3)*($C$2:$C$417="R")))

....

Sorry, if there could be cells evaluating to "" in B2:B417, this needs
to be

=SUM(($A$2:$A$417=A3)*($B$2:$B$417<>"")*($C$2:$C$417="R")/
(MMULT(--($B$2:$B$417=TRANSPOSE($B$2:$B$417)),
($A$2:$A$417=A3)*($C$2:$C$417="R"))+($B$2:$B$417="")))

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
Easy formula: In English (no cryptic symbols)
Data visualized with Table and Chart.
http://www.mediafire.com/file/mmmmdzzr0z0/09_03_09.xlsx