Count Duplicate Values of a Certain Cell in a Table but Only Count for One Instance in a Certain Range Within Table

psmi123 · Aug 7, 2018

Lets use C2.2 in Cell C2 for an example . I know how to count the number of occurrences of a certain cell within a table. However I want to be able to exclude the count of duplicate within a zone. In the table C2.2 appears 4 times. However I want to exclude duplicates within the zones and only have it count once per zone. Another example: C.4 occurs 4 times in the table but 3 of those occurrences are within zone 5 of ice cream therefore I only want it to be counted once within Zone 5, therefore i want the total number occurrences of C.4 to be 2. I am going to fill out F2:H9 on what value is supposed to shown. But I need to know how to do this without counting manually, any advice would be greatly appreciated. The Data set I am using is very large and it would take too long to count manually and I want the file to update itself.

Sector	Zone	Code 1	Code 2	Code 3	Code 1 Occurrences	Code 2 Occurrences	Code 3 Occurrences
Ice Cream	1	C2.2	C.4	C3.5	3	2	3
Ice Cream	1	C2.2	C3.5	R4.7	3	3	2
Ice Cream	2	C2.2	F3.4	U7.9	3	4	3
Ice Cream	3	C3.5	F3.4	U7.9	3	4	3
Ice Cream	4	F3.4	U7.9	R4.7	4	3	2
Ice Cream	5	C2.2	C.4	C3.5	3	2	3
Ice Cream	5	C2.2	C.4	F3.4	3	2	4
Ice Cream	5	C3.5	C.4	F3.4	3	2	4

Thank you in advance for the help!

Marcelo Branco · Aug 7, 2018

Try

Array formula in F2 copied across and down
=SUM(IF(FREQUENCY(IF(MMULT(--($C$2:$E$9=C2),{1;1;1})>0,MATCH($B$2:$B$9,$B$2:$B$9,0)),ROW($B$2:$B$9)-ROW($B$2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

psmi123 · Aug 7, 2018

This works! However, what if there are multiple sectors and I only want the codes counted within the sector as well, how would I do that? Also do you mind explaining the formula? So i can translate it to the bigger file?

Marcelo Branco · Aug 7, 2018

To handle multiple sectors we need to add a new criteria (column A)

	A	B	C	D	E	F	G	H
1	Sector	Zone	Code 1	Code 2	Code 3	Code 1 Occurrences	Code 2 Occurrences	Code 3 Occurrences
2	Ice Cream	1	C2.2	C.4	C3.5	3	2	3
3	Ice Cream	1	C2.2	C3.5	R4.7	3	3	2
4	Ice Cream	2	C2.2	F3.4	U7.9	3	4	3
5	Ice Cream	3	C3.5	F3.4	U7.9	3	4	3
6	Ice Cream	4	F3.4	U7.9	R4.7	4	3	2
7	Ice Cream	5	C2.2	C.4	C3.5	3	2	3
8	Ice Cream	5	C2.2	C.4	F3.4	3	2	4
9	Ice Cream	5	C3.5	C.4	F3.4	3	2	4
10	xxxx	1	C2.2	C.4	C3.5	2	1	2
11	xxxx	1	C2.2	C3.5	R4.7	2	2	1
12	xxxx	2	C2.2	F3.4	U7.9	2	2	2
13	xxxx	3	C3.5	F3.4	U7.9	2	2	2

Array formula in F2 copied across and down
=SUM(IF(FREQUENCY(IF($A$2:$A$13=$A2,IF(MMULT(--($C$2:$E$13=C2),{1;1;1})>0,MATCH($B$2:$B$13,$B$2:$B$13,0))),ROW($B$2:$B$13)-ROW($B$2)+1),1))
Ctrl+Shift+Enter

To understand what the formula does, step by step, try Formulas > Evaluate formula

M.

psmi123 · Aug 7, 2018

So its working in the sample problem but I cant translate it to my file.

=SUM(IF(FREQUENCY(IF($A$2:$A$13=$A2,IF(MMULT(--($C$2:$E$13=C2),{1;1;1})>0,MATCH($B$2:$B$13,$B$2:$B$13,0))),ROW($B$2:$B$13)-ROW($B$2)+1),1))

I tried to evaluate it but its not making sense, could u please explain to me the part of the formula thats in red?

Marcelo Branco · Aug 7, 2018

The formula is a bit complicated and difficult to explain. To understand it, you must know how to use the FREQUENCY function to count without duplicates (count unique).

In this case, it is necessary to consider two criteria, i.e.: same sector - which is verified by $A$2:$A$13=$A2; and on which rows the value in C2 is present - which is tested by MMULT(--($C$2:$E$13=C2),{1;1;1})>0

Could you show us a sample of data that represents your real scenario? So I could try to adapt the formula.

M.

psmi123 · Aug 8, 2018

That makes sense! Below is a better sample of the data

2 More questions as well if you don't mind answering:

1.) What does {1;1;1})>0 mean? Could you please explain to me what ROW($B$2:$B$13)-ROW($B$2)+1),1)) is doing?

2.) For -ROW($B$2)+1),1)) what is the subtracting doing? Also why would $B$2 stay the same if its only referencing one cell the time? Would it make more sense to remove the $ before the 2? and what is the function of that plus one?

Thank you so much for your help, it is greatly appreciated!

Sector	Zone	Column C	Column D	Column E	Column F	Column G	Column H	Column I	Code 1	Code 2	Code 3	Code 4	Code 5	Code 6	Code 7	Code 8	Code 9	Code 10	Code 11	Code 12	Code 13	Code 14	Code 15	Code 16	Code 17	Code 18	Code 19	Code 20	Code 21	Code 1 Occurrence	Code 2 Occurrence	Code 3 Occurrence	Code 4 Occurrence	Code 5 Occurrence	Code 6 Occurrence	Code 7 Occurrence	Code 8 Occurrence	Code 9 Occurrence	Code 10 Occurrence	Code 11 Occurrence	Code 12 Occurrence	Code 13 Occurrence	Code 14 Occurrence	Code 15 Occurrence	Code 16 Occurrence	Code 17 Occurrence	Code 18 Occurrence	Code 19 Occurrence	Code 20 Occurrence	Code 21 Occurrence
Unnamed	16	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Unnamed	36	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Unnamed	38	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Unnamed	48	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Unnamed	50	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	2	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	-	-	-	-	U1.13.2.0.0.0	U1.0.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	5	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.18.1.0.0.0	U1.18.3.0.0.0	U1.18.0.0.0.0	U1.18.4.0.0.0	U1.18.0.0.0.0	U1.18.0.0.0.0	G3.3.0.0.0.0	U1.18.0.0.0.0	U1.15.0.0.0.0	U1.1.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	6	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.18.4.0.0.0	U1.15.1.0.0.0	U1.2.5.0.0.0	U1.15.2.0.0.0	U1.15.3.0.0.0	U1.9.0.0.0.0	U1.2.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	8	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.1.7.0.0.0	U1.16.0.0.0.0	U1.1.0.0.0.0	U1.1.0.0.0.0	U1.13.1.0.0.0	U1.16.3.0.0.0	U1.16.1.0.0.0	U1.2.5.0.0.0	TBD	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	9	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.10.0.0.0.0	U1.10.0.0.0.0	U1.10.3.0.0.0	U1.10.4.0.0.0	U1.2.0.0.0.0	U1.20.0.0.0.0	U1.13.2.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	10	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.4.0.0.0.0	U1.2.0.0.0.0	U1.18.4.0.0.0	U1.9.0.0.0.0	U1.20.0.0.0.0	U1.1.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	12	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.18.0.0.0.0	TBD	TBD	TBD	TBD	TBD	TBD	TBD	TBD	TBD	TBD	-	-	-	-	-	-	-	-	-	-
Ice Cream	13	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.21.7.0.0.0	U1.21.7.0.0.0	U1.21.7.0.0.0	U1.21.7.0.0.0	U1.21.7.0.0.0	U1.21.7.0.0.0	G4.1.0.0.0.0	U1.18.0.0.0.0	U1.21.3.0.0.0	U1.4.0.0.0.0	U1.2.0.0.0.0	U1.5.0.0.0.0	-	-	-	-	-	-	-	-	-
Ice Cream	15	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.18.0.0.0.0	U1.15.2.0.0.0	U1.0.0.0.0.0	U1.8.0.0.0.0	G5.4.1.0.0.0	U1.18.4.0.0.0	U1.2.1.0.0.0	G4.3.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	17	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G1.3.0.0.0.0	G1.3.0.0.0.0	G1.8.4.0.0.0	G1.10.0.0.0.0	G1.9.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	17	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G1.2.0.0.0.0	G1.3.0.0.0.0	U1.12.0.0.0.0	G1.3.3.0.0.0	G1.10.0.0.0.0	G1.9.0.0.0.0	G1.15.2.0.0.0	G4.2.0.0.0.0	U1.2.1.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	17	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G1.2.0.0.0.0	G1.3.0.0.0.0	U1.12.0.0.0.0	G1.4.0.0.0.0	G1.1.0.0.0.0	G1.3.3.0.0.0	G1.8.1.0.0.0	G1.8.2.0.0.0	G1.8.3.0.0.0	G1.8.4.0.0.0	G1.10.0.0.0.0	G1.9.0.0.0.0	G1.6.0.0.0.0	G1.15.2.0.0.0	G1.11.0.0.0.0	G1.13.0.0.0.0	G4.2.0.0.0.0	U1.2.1.0.0.0	-	-	-
Ice Cream	18	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	TBD	TBD	TBD	TBD	TBD	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	21	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	TBD	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	27	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G1.3.0.0.0.0	G1.15.1.0.0.0	G5.1.5.0.0.0	G1.13.0.0.0.0	G4.2.5.0.0.0	U1.4.0.0.0.0	U1.2.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	27	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G1.3.0.0.0.0	G1.15.1.0.0.0	G5.1.5.0.0.0	G4.2.5.0.0.0	U1.4.0.0.0.0	U1.2.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	27	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G1.3.0.0.0.0	G1.15.1.0.0.0	G5.1.5.0.0.0	G1.13.0.0.0.0	G4.2.5.0.0.0	U1.4.0.0.0.0	U1.2.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	27	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G1.3.0.0.0.0	G1.15.1.0.0.0	G5.1.5.0.0.0	G1.13.0.0.0.0	G4.2.5.0.0.0	U1.4.0.0.0.0	U1.2.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	28	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G1.3.3.0.0.0	G4.2.0.0.0.0	G5.1.2.1	G5.1.2.2.0.0	U1.11.5.0.0.0	G7.3.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	29	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	TBD	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	30	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.20.1.0.0.0	U1.20.2.0.0.0	U1.20.3.0.0.0	U1.20.4.0.0.0	U1.20.0.0.0.0	N/A	U1.10.3.0.0.0	U1.2.0.0.0.0	U1.20.4.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	31	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.0.0.0.0.0	U1.2.0.0.0.0	U1.9.3.0.0.0	U1.9.6.0.0.0	U1.9.5.0.0.0	U1.9.6.0.0.0	U1.9.6.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	33	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G5.4.1.0.0.0	G5.4.5.0.0.0	G5.4.4.0.0.0	G5.5.0.0.0.0	G5.4.3.0.0.0	U1.2.0.0.0.0	U1.11.5.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	35	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	TBD	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	37	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G5.1.1.0.0.0	G5.1.3.0.0.0	G5.1.2.0.0.0	G5.1.4.0.0.0	G5.1.3.2.0.0	G5.1.3.3.0.0	G5.1.3.1.0.0	U1.17.2.0.0.0	U1.2.0.0.0.0	U1.11.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	37	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G5.1.1.0.0.0	G5.1.2.0.0.0	G5.1.4.0.0.0	U1.17.2.0.0.0	U1.11.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	37	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G5.1.1.0.0.0	G5.1.3.0.0.0	G5.1.2.0.0.0	G5.1.4.0.0.0	G5.1.3.2.0.0	G5.1.3.3.0.0	G5.1.3.1.0.0	U1.17.2.0.0.0	U1.2.0.0.0.0	U1.11.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	39	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	TBD	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	40	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	N/A	G5.4.4.2.0.0	G5.4.4.1.0.0	U1.11.3.0.0.0	G5.3.1.0.0.0	G5.3.2.0.0.0	G5.5.0.0.0.0	U1.2.0.0.0.0	U1.11.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	41	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	TBD	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	42	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	TBD	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	43	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G7.2.1.0.0.0	G7.2.2.0.0.0	G7.2.3.0.0.0	G7.2.4.0.0.0	G5.4.7.1.0.0	G5.1.4.0.0.0	G5.4.7.2.0.0	G7.3.4.0.0.0	U1.16.3.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	44	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	TBD	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	44	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	TBD	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	45	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.18.0.0.0.0	U1.2.0.0.0.0	U1.12.B	U1.16.3.0.0.0	U1.9.0.0.0.0	U1.13.2.0.0.0	U1.14.0.0.0.0	U1.16.1.0.0.0	U1.1.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	46	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.19.0.0.0.0	G5.4.2.0.0.0	U1.19.0.0.0.0	G5.4.0.0.0.0	U1.9.0.0.0.0	U1.9.6.0.0.0	U1.2.1.4.0.0	U1.12.0.0.0.0	U1.0.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-
Ice Cream	47	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.19.0.0.0.0	U1.19.0.0.0.0	U1.19.0.0.0.0	U1.19.0.0.0.0	U1.10.0.0.0.0	U1.2.1.4.0.0.0	U1.0.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Frozen Yogurt	7	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.17.1.4.0.0	U1.17.1.3.0.0	U1.2.0.0.0.0	U1.17.2.1.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Frozen Yogurt	15	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.18.0.0.0.0	U1.15.2.0.0.0	U1.0.0.0.0.0	U1.8.0.0.0.0	G5.4.1.0.0.0	U1.18.4.0.0.0	U1.2.1.0.0.0	G4.3.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-	-
Frozen Yogurt	6	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.18.4.0.0.0	U1.15.1.0.0.0	U1.2.5.0.0.0	U1.15.2.0.0.0	U1.15.3.0.0.0	U1.16.1.0.0.0	U1.16.0.0.0.0	U1.9.0.0.0.0	U1.2.0.0.0.0	TBD	-	-	-	-	-	-	-	-	-	-	-
Frozen Yogurt	46	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	U1.19.0.0.0.0	G5.4.2.0.0.0	U1.19.0.0.0.0	G5.4.0.0.0.0	U1.9.0.0.0.0	U1.9.6.0.0.0	U1.2.1.4.0.0	U1.12.0.0.0.0	U1.0.0.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-
Frozen Yogurt	29	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	TBD	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Frozen Yogurt	17	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G1.2.0.0.0.0	G1.3.0.0.0.0	U1.12.0.0.0.0	G1.8.3.0.0.0	G1.10.0.0.0.0	G1.9.0.0.0.0	G1.15.2.0.0.0	G1.13.0.0.0.0	G4.2.0.0.0.0	U1.2.1.0.0.0	-	-	-	-	-	-	-	-	-	-	-
Frozen Yogurt	17	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	Non Relevant Info	G1.2.0.0.0.0	G1.3.0.0.0.0	U1.12.0.0.0.0	G1.8.3.0.0.0	G1.10.0.0.0.0	G1.9.0.0.0.0	G1.15.2.0.0.0	G4.2.0.0.0.0	U1.2.1.0.0.0	-	-	-	-	-	-	-	-	-	-	-	-

Marcelo Branco · Aug 8, 2018

{1;1;1} is used as the second argument of MMULT to generate an array with one column. The vertical array, resultant of MMULT, will contain 1 for those rows that contain the code in question and 0 for the rows that do not contain the code in question. For this reason the formula uses MMULT(....)>0 to identify the rows that contain the code in question.

I used a vertical array with 3 elements because there are 3 columns with codes (columns C to E). With four columns with codes you should use, as the second argument of MMULT, a vertical array with 4 elements {1;1;1;1}. With 5 columns an array with 5 elements, and so on.

ROW($B$2:$B$13)-ROW($B$2)+1 generates a vertical array like
{1;2;3;4;5;6;7;8;9;10;11;12;13}

I'm noticing that you're not familiar with this type of formula. So to have a chance to understand the formula and to begin with, I recommend you watch this video (long, but excellent explanation of how to use the FREQUENCY function to count unique)
https://www.youtube.com/watch?v=uUrI8hoj8BA

M.

psmi123 · Aug 8, 2018

All I had to do was change the array to fit my range! Thank you so much for your help, and I will watch that video!

psmi123 · Aug 8, 2018

So I'm currently watching the video and I'm only confused on SUM(IF(FREQUENCY(IF($A$2:$A$13=$A2,IF(MMULT(--($C$2:$E$13=C2),{1;1;1})>0,MATCH($B$2:$B$13,$B$2:$B$13,0))),ROW($B$2:$B$13)-ROW($B$2)+1),1))

I still don't understand why there would be a $ in front of the 2? Wouldn't they all just be pulling from the same zone? He didn't really go too in depth with that part so I'm still trying to understand it.

Thank you!

-P

Count Duplicate Values of a Certain Cell in a Table but Only Count for One Instance in a Certain Range Within Table

psmi123

New Member

Excel Facts

Marcelo Branco

MrExcel MVP

psmi123

New Member

Marcelo Branco

MrExcel MVP

psmi123

New Member

Marcelo Branco

MrExcel MVP

psmi123

New Member

Marcelo Branco

MrExcel MVP

psmi123

New Member

psmi123

New Member

Similar threads

Forum statistics

Share this page

Count Duplicate Values of a Certain Cell in a Table but Only Count for One Instance in a Certain Range Within Table

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock