Issue with formula recognising some entries

[ozbeachbum]

Hi,
I am using the following formula and for some unknown reason it will pick up most of the entries but with some it spits the dummy if they have or have not certain text of symbols.

=SUM(IF(COUNTIFS($IX$70:$IX$340,$IX$70:$IX$340,$JR$70:$JR$340,"<>"),1/COUNTIFS($IX$70:$IX$340,$IX$70:$IX$340&""),0))

Unable to use XL2BB as the number of cells is to big and don't see anywhere a file can be attached so I have uploaded an image.
Any thoughts or suggestions would be greatly appreciated.
Cheers.

 

[Eric W]

First, you don't necessarily need to show the whole range of cell with XL2BB, just a representative sample. If you can find a smaller section that shows the issue, that's usually enough.

Second, that formula looks to be the "old-fashioned" way to count unique values. Is that what you're doing? In Excel 2021 and Excel 365 there are newer functions that do the same thing much simpler. I believe your formula could be replaced with:

=ROWS(UNIQUE(FILTER(IX70:IX340,IX70:IX340<>"")))

You can use the FILTER to ignore certain text or symbols too.

 

[ozbeachbum]

Thank you so much for your reply.
I am away at the moment but as soon as I return I will let you know how I go.

 

[ozbeachbum]

Still having issues, trust that mini sheet might help.
My endeavour is to have the formula calculate the number of entries in column IX that have a corresponding entry in column JR.

240802 error formula IX JR.xlsx
	IW	IX	IY	IZ	JA	JB	JC	JD	JE	JF	JG	JH	JI	JJ	JK	JL	JM	JN	JO	JP	JQ	JR	JS	JT	JU	JV
62		Eric's Formula		07
63	Row/Column	IX																				JR
64
65		Formatted as GENERAL, Formatting as TEXT makes no difference.		06
66
67
68				06																		7
69
70	70	Bogangar Body Corp:	1					01														DE		1
71
72	72	Expenses Redraw Average:	1					01														DE		2		If there is a COLON at the END it is NOT COUNTED.
73
74	74	Mortgage Payment Fortnightly Minimum:	1									03		04
75
76	76	Mortgage Payment Fortnightly Minimum:	1									03		04
77
78	78	Mortgage Payment Fortnightly Minimum:	1									03		04
79
80	80	Blank Row:	1					01														DE		3
81
82	82	Southport:	1					01														DE		4
83
84	84	Expenses Redraw Average:	1																			DE		5
85
86	86	RES02 Body Corporation:	1									03		04								de		6
87
88	88	Gold Coast City Council [Rates Property]:	1									03		04								de		7
Blank (2)

Cell Formulas
Range	Formula
IZ62	IZ62	=ROWS(UNIQUE(FILTER(IX70:IX340,IX70:IX340<>"")))
IZ65,IZ68	IZ65	=SUM(IF(COUNTIFS($IX$70:$IX$340,$IX$70:$IX$340,$JR$70:$JR$340,"<>"),1/COUNTIFS($IX$70:$IX$340,$IX$70:$IX$340&""),0))
JR68	JR68	=COUNTIF(JR70:JR340,"?*")
JT70	JT70	=IF(IX70<>"",1,0)
IW72,IW88,IW86,IW84,IW82,IW80,IW78,IW76,IW74	IW72	=IW70+2
JT72,JT88,JT86,JT84,JT82	JT72	=IF(IX72<>"",JT70+1,0)
JT80	JT80	=IF(IX80<>"",JT72+1,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
JR70:JR207	Cell Value	contains "DE"	text	YES
IX68	Expression	=$JR$66>#REF!	text	YES
IX68	Expression	=$JR$66=#REF!	text	YES
IX68	Expression	=$JR$66<#REF!	text	YES
JR68	Expression	=$JR$66>#REF!	text	YES
JR68	Expression	=$JR$66=#REF!	text	YES
JR68	Expression	=$JR$66<#REF!	text	YES
JJ70:JJ207	Cell Value	=4	text	YES
JP70:JP207	Cell Value	=7	text	YES
JN70:JN207	Cell Value	=6	text	YES
JL70:JL207	Cell Value	=5	text	YES
JD70:JD207	Cell Value	=1	text	YES
JF70:JF207	Cell Value	=2	text	YES
JH70:JH207	Cell Value	=3	text	YES

 

[ozbeachbum]

I have done more testing and have ascertained that the original formula I provided will not count any repeated entries in IX.
Hoping this helps.

 

[Eric W]

Try:

=ROWS(UNIQUE(FILTER(IX70:IX340,(JR70:JR340<>"")*(RIGHT(JR70:JR340)<>";"))))

 

[ozbeachbum]

Thanks,
However it still ignores any duplicates.
Cheers,
Dave.

 

[Eric W]

I thought you didn't want duplicates? If you do, just take out the UNIQUE:

=ROWS(FILTER(IX70:IX340,(JR70:JR340<>"")*(RIGHT(JR70:JR340)<>";")))

 

[ozbeachbum]

Thank you so much for all your time, greatly appreciated.
Cheers,
Dave.