Help needed !

[stvK23]

Hi all

I am trying to write a formula in several cells to count unique records and to return the count of distinct records or sum of.
But not having any luck as there are variations on what I have seen and not making sense to me.

The table below provides the data.

Column 1	Column 2	Column 3	Column 4
350350	51110754	247745	0
350350	51116014		0
350350	51117956	247942	1
350350	51124999	248107	0
350350	51126121	248136	0
350350	51127311	248176	0
350350	51127311	248177	1
350350	51127311	248177	0
350350	51132166		0
350350	51134389	248355	1
350350	51137817	248458	0
350350	51138092	248462	0
350350	51140089		0
350350	51140813		0
350350	51141228	248540	0
350350	51141964	248549	1
350350	51142639		0
350350	51147527	248677	1
350350	51151164	248760	0
350350	51152255	248793	1
350350	51154526	248846	0
350350	51156813	248911	1
350350	51156873	248918	0
350350	51160742	249023	0
350350	51161057		0
350350	51161167	249032	0
350350	51163084	249088	0
350350	51163104	249087	0
350350	51163145	249091	1
350350	51165697	249162	0
350350	51165728		0
350350	51168150	249233	0
350350	51170345	249308	0
350350	51171401	249335	0
350350	51172742		0
350350	51176634		0
350350	51177341		0
350350	51177538		0

Where Column 1 = 350350, then return the count of unique or distinct records from Column 2 and not to count any blank fields in Column 2	Where Column 1 = 350350, then return the count unique or distinct records from Column 3 and not to count any blank fields in Column 3	Where Column 1 = 350350 and for where Column 3 are only unique or distinct where Column 4 is 1, then return the sum of Column 4 for the unique or distinct records in Column 3 where its corresponding record in Column 4 is 1 .
X	Y	Z
The result should return 37	The result should return 26	The result should return 8

Would really appreciate if someone could provide the formulas for X, Y and Z, please?

Additionally, if the formula for X, Y and Z are in one sheet (lets say Worksheet 1) and the data source if from another sheet (Worksheet 2) of the same file, would the formula be different? If so, would also appreciate to know how the formula is to be written?

Thanks to you all in advance.

S

 

[Fluff]

What version of Excel are you using?
Please update your account details as it affects which functions you can use.

 

[stvK23]

I'm using Excel 2016.

 

[Fluff]

Ok, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	Column 1	Column 2	Column 3	Column 4			X	Y	Z
2	350350	51110754	247745	0		350350	36	26	8
3	350350	51116014		0
4	350350	51117956	247942	1
5	350350	51124999	248107	0
6	350350	51126121	248136	0
7	350350		248176	0
8	350350	51127311	248177	1
9	350350	51127311	248177	0
10	350350	51132166		0
11	350350	51134389	248355	1
12	350350	51137817	248458	0
13	350350	51138092	248462	0
14	350350	51140089		0
15	350350	51140813		0
16	350350	51141228	248540	0
17	350350	51141964	248549	1
18	350350	51142639		0
19	350350	51147527	248677	1
20	350350	51151164	248760	0
21	350350	51152255	248793	1
22	350350	51154526	248846	0
23	350350	51156813	248911	1
24	350350	51156873	248918	0
25	350350	51160742	249023	0
26	350350	51161057		0
27	350350	51161167	249032	0
28	350350	51163084	249088	0
29	350350	51163104	249087	0
30	350350	51163145	249091	1
31	350350	51165697	249162	0
32	350350	51165728		0
33	350350	51168150	249233	0
34	350350	51170345	249308	0
35	350350	51171401	249335	0
36	350350	51172742		0
37	350350	51176634		0
38	350350	51177341		0
39	350350	51177538		0
Contacts

Cell Formulas
Range	Formula
G2	G2	=SUM(--(FREQUENCY(IF(A2:A39=F2,B2:B39),B2:B39)>0))
H2	H2	=SUM(--(FREQUENCY(IF(A2:A39=F2,C2:C39),C2:C39)>0))
I2	I2	=SUM(--(FREQUENCY(IF(A2:A39=F2,IF(D2:D39=1,B2:B39)),B2:B39)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Please don't forget to update your account details & then scroll down & click save.

 

[stvK23]

Thanks Fluff ! The last formula is returning #Value!.
Additionally, how can I incorporate the value that you have put in cell F2 into the formula rather than having to reference cell F2 as I will have other numbers than 350350?

 

[stvK23]

I got the formula to work for it to return 8 by changing to B2:B39 to C2:C39 and had to hit Crtl+Shift+Enter.

So regarding the reference cell F2, I can just change it to 350350 and it works fine.

Thanks very much.

 

[Fluff]

You're welcome & thanks for the feedback.