Numbers formula

[rctneil]

Hey,

Basically I need to get a count of some items but in a specific way and was wondering if a spreadsheet guru would be able to advise me how to go about it.

Basically I have a list of items in A, it's house in B and it's location 1, 2, 3 and 4 in C, D, E and F.

Locations are split up into 3/4 parts so eg:

Item 1 | House 1 | Blackpool | Lancashire | United Kingdom

I am trying to count the house but:

- 1 House can have multiple items

AND

- Houses can have the same name but will have different locations 1, 2, 3 and 4.

So basically I need to count the distinctly unique house names but if a duplicate house name exists then fallback to check the 4 locations. If the locations are identical then skip to the next house otherwise add to the count.

Anyone able to help me solve this?

Neil

 

[ricky1981]

Sounds like the easiest way would be to set column G to concatenate columns B-F and then count the unique values in that column. I guess what you ultimately are after is the number of unique houses?

Put this in row 2 of column G and copy it as far down as your data goes

=CONCATENATE(C2,D2,E2,F2)

Then somewhere else enter this, replacing G6 with the last cell you copied the formula above into:

=SUMPRODUCT((G2:G6<>"")/COUNTIF(G2:G6,G2:G6))

Should do the trick