Using (SUM(IF(FREQUENCY....to determine unique values

[Ivor Davies]

I am trying to determine the number of individual people used on a project.

I have 3 Columns of Data - e.g.

Project ID 1 Project ID 2 Person Name
Project A Project 123 Joe Bloggs
Project A Project 456 Joe Bloggs
Project A Project 456 Mary Little-Lamb
Project B Project 123 Jim Smith
Project B Project 123 Mary Little-Lamb

The forumla I need will determine the number of unique people against the
unique projects in both columns. So the end result will look like this:

Project ID 1 Project ID 2 No. People
Project A Project 123 1
Project A Project 456 2
Project B Project 123 2

I've been trying to use the (SUM(IF(FREQUENCY.... to determine the number of
unique names against the project ID, but I'm having trouble with getting the
formula to lookup the applicable reference and then return the value, all I
get is the entire number of unique values regardless of which Project they
are against.

I appreciate any help you can give.

Thanks

 

[Jacob Skaria]

Try the below formula in Sheet2 with your data in Sheet1

--Make sure there are no blank entries in the range Sheet1 A2:A6
--In Sheet2 D2 apply the below array formula and copy down to D3 and D4

=SUM(N(FREQUENCY(IF((Sheet1!$A$2:$A$6=A2)*
(Sheet1!$B$2:$B$6=B2),MATCH(Sheet1!$C$2:$C$6,Sheet1!$C$2:$C$6,)),
MATCH(Sheet1!$C$2:$C$6,Sheet1!$C$2:$C$6,))>0))

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>}"


If this post helps click Yes

 

[Herbert Seidenberg]

Excel 2007 PivotTable
No formulas needed.
Three ways.
http://www.mediafire.com/file/yz2jbejxwkr/11_05_09b.xlsx