List unique values and count cell values with different conditions

nahaku

New Member
Joined
Mar 19, 2020
Messages
13
Office Version
365, 2019
Platform
Windows
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

Some videos you may like

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
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about

+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
Joined
Mar 19, 2020
Messages
13
Office Version
365, 2019
Platform
Windows
Hi & welcome to MrExcel.
How about

+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
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
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
Joined
Mar 19, 2020
Messages
13
Office Version
365, 2019
Platform
Windows
yes thank you, that is pretty much it :P
 

nahaku

New Member
Joined
Mar 19, 2020
Messages
13
Office Version
365, 2019
Platform
Windows
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 .
z.jpg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
How about
=UNIQUE(FILTER(FILTER(A2:C13,(COLUMN(A2:C2)=1)+(COLUMN(A2:C2)=3)),A2:A13<>"Cheshire"))
 

nahaku

New Member
Joined
Mar 19, 2020
Messages
13
Office Version
365, 2019
Platform
Windows
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
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
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
Joined
Mar 19, 2020
Messages
13
Office Version
365, 2019
Platform
Windows
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.
 

Forum statistics

Threads
1,089,218
Messages
5,406,923
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top