Count unique cells based on column of blank cells

[monkeyharris]

Hi,

I've managed to get part of my formula to work which counts the number of unique records in column B. I only need it to run a calculation on those cells in G which are blank. Any ideas? Formula is in L5727 as i was testing it there.

2024 Woodford Proactive Calls.xls
	A	B	C	D	E	F	G	H	I	J	K	L
5726	15/03/24	1CS22555	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5727	15/03/24	1CS22555	WO	Alina Maria	Sales-i	Yes	Alina	3	45382			910
5728	15/03/24	1CS22569	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5729	15/03/24	1CS22569	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5730	15/03/24	1CS22581	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5731	15/03/24	1CS22581	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5732	15/03/24	1CS22590	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5733	15/03/24	1CS22590	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5734	15/03/24	1CS22592	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5735	15/03/24	1CS22592	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5736	15/03/24	1CS22596	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5737	15/03/24	1CS22596	WO	Alina Maria	Sales-i	Yes	Alina	3	45382
5738	15/03/24	1CS22607	WO	Alina Maria	Sales-i	Yes		3	45382
5739	15/03/24	1CS22607	WO	Alina Maria	Sales-i	Yes		3	45382
5740	15/03/24	1CS22613	WO	Alina Maria	Sales-i	Yes		3	45382
5741	15/03/24	1CS22613	WO	Alina Maria	Sales-i	Yes		3	45382
5742	15/03/24	1CS22624	WO	Alina Maria	Sales-i	Yes		3	45382
5743	15/03/24	1CS22624	WO	Alina Maria	Sales-i	Yes		3	45382
5744	15/03/24	1CS22630	WO	Alina Maria	Sales-i	Yes		3	45382
5745	15/03/24	1CS22630	WO	Alina Maria	Sales-i	Yes		3	45382
YTD_Figures

Cell Formulas
Range	Formula
H5726:H5745	H5726	=IF(I5726=45322,1,IF(I5726=45351,2,IF(I5726=45382,3,0)))
I5726:I5745	I5726	=EOMONTH(A5726,0)
L5727	L5727	=SUM(IF(FREQUENCY(IF(LEN(B2:B5789)>0,MATCH(B2:B5789,B2:B5789,0),""),IF(LEN(B2:B5789)>0,MATCH(B2:B5789,B2:B5789,0),""))>0,1))
G5726:G5737	G5726	=VLOOKUP(B5726,WO_Bronze!$B$2:$J$947,9,FALSE)

 

[monkeyharris]

If I am understanding correctly, try this instead of the formula you currently have in L5727

=LET(r,B2:B5789,ROWS(UNIQUE(FILTER(r,r<>""))))

For the added condition, try

=LET(r,B2:B5789,IFNA(ROWS(UNIQUE(FILTER(r,(r<>"")*(G2:G5789=""),NA()))),0))

Hi Peter,

As mentioned, the formula's you've provided which start with LET always disappear and replaced with {=#VALUE!}. Below is what's shown in column D.

2024 Woodford Proactive Calls.xls
	A	B	C	D	E	F	G	H	I	J
1	Q1 WOODFORD INTERNAL SALES PERFORMANCE
2	Employee	Q1 Contacted	Q1 Not Contacted	No. of Accounts with Sales	No. of Sales Generated	No of Accounts Quoted	No. of Quotes Issued	No. of Accounts with Sales-I Entries	No. of Sales-i Entries	Q1 Performance
3	Alina	104	110	61	94		32		129	48.60%
4	Harriet	59	30	32	37		29		75	66.29%
5	Jemma	37	63	41	67		37		42	37.00%
6	Jo	23	65	15	22		12		14	26.14%
7	Mita	20	37	26	42		11		10	35.09%
8	Paul	71	66	68	117		37		142	51.82%
9	Tendayi	44	43	37	54		51		41	50.57%
10	Zoe	37	54	28	45		20		22	40.66%
11	Overall	395	468	308	478		229		475	45.77%
12
13	Q1 EXTERNAL BDM PERFORMANCE
14	Employee	Q1 Contacted	Q1 Not Contacted	Accounts with Sales	No. of Sales Generated	No of Accounts Quoted	No. of Quotes Issued	No. of Accounts with Sales-I Entries	No. of Sales-i Entries	Q1 Performance
15	BDM - Jamie	68	66	96	198		71		56	50.75%
16	BDM - Jo Amos	24	4	49	189		83		85	85.71%
17	BDM - Nick	200	96	292	1253		352		217	67.57%
18	BDM - Steve	21	2	16	16		15		41	91.30%
19	Overall	313	168	453	1656		521		399	65.07%
Statistics

Cell Formulas
Range	Formula
B3:B10,B15:B18	B3	=SUMPRODUCT(--(WO_Bronze!J:J=Statistics!A3)*(WO_Bronze!N:N="Complete"))
C3:C10,C15:C18	C3	=SUMPRODUCT(--(WO_Bronze!J:J=Statistics!A3)*(WO_Bronze!N:N="No"))
D3:D10,D15:D18	D3	=#VALUE!
E3:E10,E15:E18	E3	=COUNTIFS(YTD_Figures!$E$2:$E$8394,"Sales Order",YTD_Figures!$G$2:$G$8394,A3,YTD_Figures!$H$2:$H$8394,1)+COUNTIFS(YTD_Figures!$E$2:$E$8394,"Sales Order",YTD_Figures!$G$2:$G$8394,A3,YTD_Figures!$H$2:$H$8394,2)+COUNTIFS(YTD_Figures!$E$2:$E$8394,"Sales Order",YTD_Figures!$G$2:$G$8394,A3,YTD_Figures!$H$2:$H$8394,3)
B11:E11,G11,I11	B11	=SUM(B3:B10)
I3:I10,I15:I18	I3	=COUNTIFS(YTD_Figures!$E$2:$E$8394,"Sales-i",YTD_Figures!$G$2:$G$8394,A3,YTD_Figures!$H$2:$H$8394,1)+COUNTIFS(YTD_Figures!$E$2:$E$8394,"Sales-i",YTD_Figures!$G$2:$G$8394,A3,YTD_Figures!$H$2:$H$8394,2)+COUNTIFS(YTD_Figures!$E$2:$E$8394,"Sales-i",YTD_Figures!$G$2:$G$8394,A3,YTD_Figures!$H$2:$H$8394,3)
J3:J10,J15:J18	J3	=B3/COUNTIF(WO_Bronze!J:J,Statistics!A3)
J11,J19	J11	=B11/SUM(B11:C11)
G3:G10,G15:G18	G3	=COUNTIFS(YTD_Figures!$E$2:$E$8394,"Sales Quote",YTD_Figures!$G$2:$G$8394,A3,YTD_Figures!$H$2:$H$8394,1)+COUNTIFS(YTD_Figures!$E$2:$E$8394,"Sales Quote",YTD_Figures!$G$2:$G$8394,A3,YTD_Figures!$H$2:$H$8394,2)+COUNTIFS(YTD_Figures!$E$2:$E$8394,"Sales Quote",YTD_Figures!$G$2:$G$8394,A3,YTD_Figures!$H$2:$H$8394,3)
B19:E19,G19,I19	B19	=SUM(B15:B18)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

the formula's you've provided which start with LET always disappear and replaced with {=#VALUE!}.

Are you able to do the following?

• Confirm that you are doing this in excel 365
• Put the working formulas back in
• Remove or disguise any sensitive data
• Save, close and don't re-open
• Upload a copy of the workbook to Dropbox or OneDrive or Google Drive etc
• Provide a publicly shared link here

I can then take a closer look and see if I can replicate the issue.

 

[monkeyharris]

Are you able to do the following?

• Confirm that you are doing this in excel 365
• Put the working formulas back in
• Remove or disguise any sensitive data
• Save, close and don't re-open
• Upload a copy of the workbook to Dropbox or OneDrive or Google Drive etc
• Provide a publicly shared link here

I can then take a closer look and see if I can replicate the issue.

Hi Peter,
I found the problem, by default it saved as a 97-2013 file. I changed it to xlsx. and the formula's remained this time.

 

[Peter_SSs]

Cheers, glad you got it sorted. Thanks for letting us know.