CODEWITHSUNDEEP
excelstringexcel-formulacountalphanumeric
Nick Hatzopoulos
Nick Hatzopoulos

Reputation: 1

Excel String counts Alpha or Numeric

I have the following data:

Column ‘A’ is a Numeric Account Column “C” is the Account related to an Alphanumeric Code in ‘D’

Table of Accounts and Codes

Table of Accounts and Codes

For each Account, I need a count of Codes that have Len = 6 I have tried using:

=COUNTIFS(C$2:$C$10,A3,D$2:D$10,"??????")

I was looking for this to return a result = 2 for Account #2

But this formula only considers ‘D’ Values of LEN =6 that are Strings, so it returned a result =1.

It does not consider numeric Strings of LEN=6.

How can I get it to work with any Len=6 (Alpha or Numeric )? Thanks!

Upvotes: 0

Views: 51

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27438

Here is one way of doing this using SUMPRODUCT() function:

enter image description here

=SUMPRODUCT((LEN(D$2:D$10)=6)*(A2=C$2:C$10))

Or, Can use a LAMBDA() helper function to return the single output as dynamic array.

enter image description here

=LET(
     _Data, A2:B10,
     _Account, TAKE(_Data,,1),
     _Uniq, UNIQUE(_Account),
     _Count, MAP(_Uniq, LAMBDA(x, SUM((x=_Account)*(LEN(TAKE(_Data,,-1))=6)))),
     VSTACK({"Account","Value"},HSTACK(_Uniq,_Count)))

Or, If you have access to GROUPBY() function then:

=GROUPBY(A2:A10,A2:A10,COUNT,,0,,,LEN(B2:B10)=6)

Upvotes: 1

Related Questions