# List unique values and count cell values with different conditions

#### nahaku

##### New Member
Hello, I im trying to find formulas to help me make statistics, but I do not know how to get this. The formulas I found on internet helps always only with half of the problem.
I am trying to list unique value from [A] Yelow column for example GBL0004,GBS0083,...
then it could list GBL0004 divided by Region name from Column Blue column so for example GBL0004 A , GBS0083 A, GBS0083 C1, GBS0083 B1,GBS0088 A, GBS0088 B1 ....
it basically splits the person by location where they ware working.
and then just count with Brown Columns separetly. That is somethink i might do, but I am not able to count it with that condition so it would count for unique
IF (Picker [A]+ Region name ) = SUM(C:C) { or Sum D, E, F, G......}

#### Attachments

• 53.9 KB Views: 8

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.

+Fluff.xlsm
ABJK
1County
2BuckinghamshireABuckinghamshireA
3BuckinghamshireACheshireA
4BuckinghamshireACheshireB
5BuckinghamshireACounty DurhamA
6CheshireACounty DurhamC
7CheshireBCounty DurhamC1
8CheshireA
9County DurhamA
10County DurhamC
11County DurhamC1
12County DurhamA
13County DurhamC1
Data
Cell Formulas
RangeFormula
J2:K7J2=UNIQUE(A2:B13)
Dynamic array formulas.

#### nahaku

##### New Member
Hi & welcome to MrExcel.

+Fluff.xlsm
ABJK
1County
2BuckinghamshireABuckinghamshireA
3BuckinghamshireACheshireA
4BuckinghamshireACheshireB
5BuckinghamshireACounty DurhamA
6CheshireACounty DurhamC
7CheshireBCounty DurhamC1
8CheshireA
9County DurhamA
10County DurhamC
11County DurhamC1
12County DurhamA
13County DurhamC1
Data
Cell Formulas
RangeFormula
J2:K7J2=UNIQUE(A2:B13)
Dynamic array formulas.
yes that looks promising. Is it possible to take input from for example column A and column C ?in your example you take column A and B, what is easy but I need to take input from columns far from each other. because I have some data what are imported in to excel and there we are unable to change its format.

#### Fluff

##### MrExcel MVP, Moderator
You can do that like
+Fluff.xlsm
ABCDEKL
1County
2BuckinghamshireaA1BuckinghamshireA
3BuckinghamshiredA2CheshireA
4BuckinghamshirecA3CheShireB
5BuckinghamshireA4County DurhamA
6CheshireA5County DurhamC
7CheShireB6County DurhamC1
8CheshireA7
9County DurhamA8
10County DurhamC9
11County DurhamC110
12County DurhamA11
13County DurhamC112
Data
Cell Formulas
RangeFormula
K2:L7K2=UNIQUE(FILTER(A2:C13,(COLUMN(A2:C2)=1)+(COLUMN(A2:C2)=3)))
Dynamic array formulas.

#### nahaku

##### New Member
yes thank you, that is pretty much it :P

#### nahaku

##### New Member
I tried to paste it here, but it does not work, And I cant delete that post. Is possible to add to your formula =UNIQUE(FILTER(A2:C13,(COLUMN(A2:C2)=1)+(COLUMN(A2:C2)=3))) something like either filter what will take values from range of cells to ignore those name? or I could type in the formula what names to ignore? names are like GBS0007 ,GBX5567, TWXLOASJ ? I would like to get rid of those GBS0183 for example. I got those values by using your formula .

#### Fluff

##### MrExcel MVP, Moderator
=UNIQUE(FILTER(FILTER(A2:C13,(COLUMN(A2:C2)=1)+(COLUMN(A2:C2)=3)),A2:A13<>"Cheshire"))

#### nahaku

##### New Member
Thank you for your help I wish I would know more stuff from Excel, it is very long time I work with Excel.
The formula is not working for me, there is some issue. when you look at the left site where i need that formula it says "SPILL" when i tried it on the right side with free space it showed only 1 entry and it added many empty cels too.
Anyway if This formula would work, I still could filter it only by 1 value, isnt it? Unfortunately I do not know how to use the "OR" function if it serves that purpose I could make formula to ignore one or the other...
If you want and can, is there something you could advice? No rush :P Thank you.
Screenshot: imgur.com/a/Pgt3XRp

#### Fluff

##### MrExcel MVP, Moderator
You have missed out one of the filter functions, compare your formula with the one in post#7
Also rather than posting images, please use the XL2BB add-in to post actual data to the thread

#### nahaku

##### New Member
I missed that one, Thank you, now it works.
=UNIQUE(FILTER(FILTER(Picking!B2:E201,(COLUMN(Picking!B2:E2)=2)+(COLUMN(Picking!B2:E2)=5)),Picking!B2:B201<>"GBS0183"))
one last thing, can i add more values "GBS0183" .... to ignore? Either if I type them or from some selected list. I have approximately 21 values what may occur but should not be counted for.