Distinct Counts with Multiple Criteria

B

Bentam3

Each month we produce a complex report in Excel using thousands of rows of
data. We've just been asked to add in a new table which needs to use
distinct counts based on multiple criteria. I've included an example of the
data below. How can I create a formula to complete the results table as I
have manually entered below?

A B C D
1 NAME DEPT 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 DEPT Primary Secondary
16 Finance 3 1
17 Admin 1 0
18 IT 1 1

Thanks
BT
 
J

Jacob Skaria

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Distinct Count with Criteria 3
Filters 1
Filters 1
IF statement with multiple VLOOKUPs 0
Getting unexpected gaps in a spreadsheet 1
Import txt files into Excel 7
SUMIF WITH OR 6
Duplicate with Criteria 3

Top