Count Unique Values Based On Data In Another Column 1

[CIBS]

Hi

I am using MSOFFICE 2007

What I have is a table containg the following

Region Part
WEST Table
South Table
EAST Chair
WEST Table
WEST Sofa
WEST Chair

What I want to do is have a cell that will say either WEST, SOUTH, EAST or NORTH. I then want a second cell that will lookup the information in that cell and then look up the table and count the uniques based on that value

For example

A1:B7 - Contains the table above
B10 - Contains the lookup value
C10 - the formula that looks at B10 and then lookups up A2:B7 and count unique values based on the value of B10

so if B10 contains WEST then in C10 it should display 4

Any Help

Kind Regards

Stephen

 

[MarcLodge]

Stephen,
The cell c10 should contain the following formula:

=SUM(COUNTIF(A2:B7,B10))

Hope this helps.

Marc

 

[CIBS]

Hi

I do apoligise C10 should display,

Thanks for the response though but I know of that way but that doesn't count the unique values based on B10.

Kind Regards

Stephen

 

[MarcLodge]

Sorry Stephen, I must be not understanding your requirement. I've just typed your data exactly as stated, into Excel 2007, and then added the formula as above, and it works perfect for me. The formula looks in A2:B7 for the value in B10.

Marc

 

[CIBS]

Hi

The formula works for counting West but what I want is to count the unique parts that are in the WEST

Kind Regards

Stephen

 

[MarcLodge]

Ah, I think I see. So based on your data above, you'd want to see the answer in C10 to be 3, as there are tables, charis, and sofas in West?

 

[CIBS]

Yes,

This seems to be a tricky thing to do and not sure what formula to put in

Kind Regards

Stephen

 

[sxschech]

Have you thought about applying a filter, then you wouldn't need formula to to determine the counts. With a filter, you could click on West, then click on Table to get the count, or to see the count of all Table regardless of location, you could you on table.

 

[CIBS]

Hi

I have thought about a filter but it wouldn't be practical as the table of data can expand and also there will be 4 unique counts. one per region and this information will be store on a 2nd tab once the formula works.

What will then happen is that the data on the 1st tab will be overwritten weekly.

Kind Regards

Stephen

 

[lionelhill]

I'm scared of suggesting this, but if you're still having difficulties, here's a rather messy solution. I've done it for WEST below, but you'll need to repeat for the other areas.

(1) make a new column containg the second column's data only if the first column is "WEST"
=IF(A2="WEST",B2,"")

(2) make a further column in which each cell tries to look up the contents in all previous cells, to see if it's a new occurrence. If it is new, it won't find it, and will return #NA. Otherwise it will return the value. In the same step, we can return true or false using isna:
=IF(C2<>"", ISNA(VLOOKUP(C2,$C$1:C1, 1, FALSE)), "")
Note that this formula came from cell D2. When copied down, the range will expand down column C, never quite catching up.

(3) Sum the trues over whatever range you want.
=COUNTIF(D18, TRUE)

 

[MarcLodge]

Stephen,
A way to do what you are describing is to write an SQL query for the 2nd tab that queries the data in the 1st tab. When the 1st tab has it's data refreshed you run the query in the 2nd. In the following piece of SQL the spreadsheet is call Area and the 1st worksheet is called Details.

select T.Region, count(*) as NoOfParts
from
(SELECT distinct `Details$`.Region , `Details$`.Part
FROM `h:\excel\Area`.`Details$` `Details$`) as T
group by Region

Hope this helps
Marc

 

[AnotherHiggins]

If you're up for using VBA, a custom function could be created to do this.

The first solution I concocted will work as long as a given part only appears against one region. Unfortunately, you do have parts against multiple regions, so this won't work for you. But I'm including it anyway because I nearly stubbed my brain on it and it will work in some situations:
[tab]=SUMPRODUCT(--($A$2:$A$7 = $B$10) * (1/COUNTIF($B$2:$B$7, $B$2:$B$7)))

After looking around online for ideas, I came up with the following. I'm pretty sure this will do what you want:
[tab][COLOR=blue white]=COUNT(1/FREQUENCY(MATCH(B2:B8 & "", B2:B8 & "", 0) * (A2:A8 = $B$10), ROW(B2:B8) - MIN(ROW(B2) + 1)))-1[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[MarcLodge]

John,
That's superb. I've read the formula 3 or 4 times and I still don't understand it. Marvellous stuff!

Catch a star for your genius.