Distinct Count with Criteria
 

I'm hoping somene can help me with my problem. I have few thousand rows of
data we create a large report from each month in Excel. We have now been
asked to add in a table which does distinct counts with multiple criteria. I
have added in a consise version below.

How can I create a formula which will fill in the results in the table below
to achieve the answers I've manually added in?

A B C D
1 NAME DEPARTMENT LOCATION TYPE
2 Mary Finance A Primary
3 Sally Finance B Primary
4 Sally Finance B Primary
5 Mary IT A Primary
6 John Finance B Secondary
7 John Finance B Secondary
8 David Admin A Primary
9 John IT B Secondary
10 David Finance A Primary
11 David IT C Primary
12
13
14 Results Table for Locations A & B
15 DEPARTMENT Primary Secondary
16 Finance 3 1
17 Admin 1 0
18 IT 1 1

Thanks
BT

Distinct Count with Criteria
 

Use Pivot Table, have a look in the below links for more details.

http://www.cpearson.com/excel/pivots.htm
http://www.contextures.com/xlpivot07.html
http://www.homeandlearn.co.uk/me/mes9p4.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Bentam3" wrote:

I'm hoping somene can help me with my problem. I have few thousand rows of
data we create a large report from each month in Excel. We have now been
asked to add in a table which does distinct counts with multiple criteria. I
have added in a consise version below.

How can I create a formula which will fill in the results in the table below
to achieve the answers I've manually added in?

A B C D
1 NAME DEPARTMENT LOCATION TYPE
2 Mary Finance A Primary
3 Sally Finance B Primary
4 Sally Finance B Primary
5 Mary IT A Primary
6 John Finance B Secondary
7 John Finance B Secondary
8 David Admin A Primary
9 John IT B Secondary
10 David Finance A Primary
11 David IT C Primary
12
13
14 Results Table for Locations A & B
15 DEPARTMENT Primary Secondary
16 Finance 3 1
17 Admin 1 0
18 IT 1 1

Thanks
BT

Distinct Count with Criteria
 

Please note that this is an array formula.You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=SUM(IF(FREQUENCY(IF((A2:A11<"")*(B2:B11="Finance ")*
(D2:D11="Primary"),MATCH(A2:A11,A2:A11,0)),
ROW(A2:A11)-ROW(A2)+1),1))

--
Jacob (MVP - Excel)


"Bentam3" wrote:

I'm hoping somene can help me with my problem. I have few thousand rows of
data we create a large report from each month in Excel. We have now been
asked to add in a table which does distinct counts with multiple criteria. I
have added in a consise version below.

How can I create a formula which will fill in the results in the table below
to achieve the answers I've manually added in?

A B C D
1 NAME DEPARTMENT LOCATION TYPE
2 Mary Finance A Primary
3 Sally Finance B Primary
4 Sally Finance B Primary
5 Mary IT A Primary
6 John Finance B Secondary
7 John Finance B Secondary
8 David Admin A Primary
9 John IT B Secondary
10 David Finance A Primary
11 David IT C Primary
12
13
14 Results Table for Locations A & B
15 DEPARTMENT Primary Secondary
16 Finance 3 1
17 Admin 1 0
18 IT 1 1

Thanks
BT

Distinct Count with Criteria
 

Thanks Jacob. This works like a treat when I put in the location criteria.

"Jacob Skaria" wrote:

Please note that this is an array formula.You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=SUM(IF(FREQUENCY(IF((A2:A11<"")*(B2:B11="Finance ")*
(D2:D11="Primary"),MATCH(A2:A11,A2:A11,0)),
ROW(A2:A11)-ROW(A2)+1),1))

--
Jacob (MVP - Excel)


"Bentam3" wrote:

I'm hoping somene can help me with my problem. I have few thousand rows of
data we create a large report from each month in Excel. We have now been
asked to add in a table which does distinct counts with multiple criteria. I
have added in a consise version below.

How can I create a formula which will fill in the results in the table below
to achieve the answers I've manually added in?

A B C D
1 NAME DEPARTMENT LOCATION TYPE
2 Mary Finance A Primary
3 Sally Finance B Primary
4 Sally Finance B Primary
5 Mary IT A Primary
6 John Finance B Secondary
7 John Finance B Secondary
8 David Admin A Primary
9 John IT B Secondary
10 David Finance A Primary
11 David IT C Primary
12
13
14 Results Table for Locations A & B
15 DEPARTMENT Primary Secondary
16 Finance 3 1
17 Admin 1 0
18 IT 1 1

Thanks
BT