LEFT not working in a COUNTIF formula

[TonyD1016]

I have a column of data on a table that consists of 3-digit numbers.

I need to strip the third digit and count the unique instances of the remaining two digit numbers in the column.

So on another sheet in A1 I have the following:

=(LEFT(range,2)

And that spills an array of the results I need to count.
Then B and C I put the following:

=UNIQUE(A1#)
=COUNTIF(A1#,UNIQUE(A1#))

Which returns exactly the totals I need.

So I return to my first sheet and put in:

=UNIQUE(LEFT(range,2))

It works fine, but when I go to the adjacent column and try to type the following:

=COUNTIF((LEFT(range,2),UNIQUE(LEFT(range,2)))

...I get the Excel stop warning of "There's a problem with this formula."

What gives?

 

[JoeMo]

Don't know for sure what gives, but here's an alternative to try:

=COUNTA(UNIQUE(LEFT(range,2)))

 

[TonyD1016]

Don't know for sure what gives, but here's an alternative to try:

=COUNTA(UNIQUE(LEFT(range,2)))

That formula returns the total amount of unique two-digit numbers.
What I need is a count of the total instances of each two-digit number.

 

[Fluff]

What's wrong with using

=UNIQUE(A1#)
=COUNTIF(A1#,UNIQUE(A1#))

You cannot use

=COUNTIF((LEFT(range,2),UNIQUE(LEFT(range,2)))

as countif does not accept an array as the 1st argument.

 

[RoryA]

You can only use ranges, not arrays, for anything but the criteria in COUNTIF. LEFT(range, 2) returns an array, so won't work.

 

[Fluff]

If you don't want the helper column, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	173	17	17	4		17	4
3	166	16	16	3		16	3
4	116	11	11	3		11	3
5	147	14	14	5		14	5
6	196	19	19	6		19	6
7	199	19	12	3		12	3
8	192	19	18	4		18	4
9	171	17	13	1		13	1
10	149	14
11	120	12
12	198	19
13	187	18
14	120	12
15	110	11
16	181	18
17	142	14
18	139	13
19	196	19
20	117	11
21	142	14
22	180	18
23	176	17
24	165	16
25	197	19
26	140	14
27	182	18
28	160	16
29	121	12
30	177	17
31
Main

Cell Formulas
Range	Formula
B2:B30	B2	=LEFT(A2:A30,2)
C2:C9	C2	=UNIQUE(B2#)
D2:D9	D2	=COUNTIFS(B2#,C2#)
F2:G9	F2	=LET(Rng,A2:A30,l,LEFT(Rng,2),u,UNIQUE(l),CHOOSE({1,2},u,MMULT(--(TRANSPOSE(l)=u),SEQUENCE(ROWS(Rng),,,0))))
Dynamic array formulas.

 

[Fluff]

Or a simpler version

=LET(Rng,A2:A30,l,LEFT(Rng,2),u,UNIQUE(l),CHOOSE({1,2},u,COUNTIFS(Rng,">="&u*10,Rng,"<"&u*10+10)))

 

[TonyD1016]

Or a simpler version

=LET(Rng,A2:A30,l,LEFT(Rng,2),u,UNIQUE(l),CHOOSE({1,2},u,COUNTIFS(Rng,">="&u*10,Rng,"<"&u*10+10)))

I could use the helper column on a hidden sheet if that would be the simplest solution. I'm mostly curious at this point if I can do without it.

The problem I am running into with both of those formulas appears to be that some of the data I am working with has a leading zeros. It's all stored as text because I don't use it in any calculations. So for instance "081", "087", and "016", which I have to count as two "08"s and one "01" respectively.

The formula from your first post ignores these leading zero instances entirely in its results, and the second formula returns the leading zeroes in the list but returns all zeroes for the counts.

 

[Fluff]

If col A is all stored as text, then that's even simpler.

=LET(Rng,A2:A30,l,LEFT(Rng,2),u,UNIQUE(l),CHOOSE({1,2},u,COUNTIFS(Rng,u&"*")))

 

[TonyD1016]

If col A is all stored as text, then that's even simpler.

=LET(Rng,A2:A30,l,LEFT(Rng,2),u,UNIQUE(l),CHOOSE({1,2},u,COUNTIFS(Rng,u&"*")))

? That worked.
So concatenating a wildcard to the unique values is what I was missing? How does that work?