SUMIF or COUNTIF using LEN for a range as a criteria

[Jacowork]

Hi guys,

I'm trying to figure something out.
A:A has values with 12 or 13 characters, I need to count the number of times each value returns.
I tried =COUNTIF(A2:A11;LEN(A2:A11)=12), but this results in 0 instead of 6.

Any help? I couldn't find any answers that worked for me.

 

[BrianMH]

Enter this as an array formula (hold ctrl shift and hit enter when inputting it).

=SUM((LEN(A2:A11)=12)*1)

 

[Jacowork]

Enter this as an array formula (hold ctrl shift and hit enter when inputting it).

=SUM((LEN(A2:A11)=12)*1)

Great.

But now, why the SUM *1?
And why the Shift Ctrl?

I'm trying to understand it too, for future references. To build logical formulas.

But again, thanks! ray:

 

[Jacowork]

Enter this as an array formula (hold ctrl shift and hit enter when inputting it).

=SUM((LEN(A2:A11)=12)*1)

And how do I add another criteria?
I would like to add a criteria where column C has an amount of 50,500 and 5000. To know how many times a 500 amount is given with a 12 character value....

123456789123	User1	50
123456789123	User2	500
1234567891234	User3	500
1234567891234	User4	500
123456789123	User5	50
1234567891234	User6	5000
123456789123	User7	5000
123456789123	User8	500
1234567891234	User9	50

<tbody>
</tbody>

For example I want to know how many users with 1234567891234 have an amount of 500.

With this info I should be able to complete my table.

Thx.

 

[BrianMH]

The *1 changes true and false into a number. Ctrl shift makes it an array formula. If you do a search on google for cpearson array formula you will get a good explanation of what they are and how useful they are.

=sumproduct(--(len(A:A)=12),--(C:C=500)) should give you what your looking for.

 

[Jacowork]

Fantastic Brian!!!!!!! You're an expert.
I'll start a new thread with another question. This one is solved, kudos.

 

[Jacowork]

Oke, forgot something...
I looked for array info on the SUMIF, as proposed.
But can't find how to add the values of C if the have 13 characters.
I found this {=SUM(IF((LEN(A:A)=13)1;0))}, but the result is not the sum of column C, but a count of cells with that length.

I would like to get the result 6050, as that is the sum of values in C corresponding to LEN 13 in column a.

I feel like you should start charging me

 

[Jacowork]

I found something basic... but solves the problem, no?
=SUMIF(A:A;"?????????????";C:C) and it works.....

Is this something good or a glitch that makes it work?

 

[BrianMH]

If it works it works

I personally would use =sumproduct(--(len(A:A)=13),C:C)