Count numbers [HELP]

[ruhat145]

Hi,

I have an assignment and i have a series of number from 1-15000.

the task is to Count numbers ending with the digit 0.
I've tried alot of things and nothing has helped.
I'm using excel 2010.

I would appericiate it very much if someone could help me to get the right formula for this task.
Thanks!

If i've provided too little information or something please tell me, because this is my first time here and i'm not that experienced.

Thanks again!

[daddylonglegs]

If your numbers are in the range A2:A1000 then try this formula

=SUMPRODUCT((RIGHT(A2:A1000)="0")+0)

[ruhat145]

I got 1500 as a result.
Thank you very much but i would appericiate it even more if you could explain it to me little.
I want to learn too.

Thank you!!! :D

[MarvinP]

Hi ruhat145 and welcome to the forum,

Here is another method that uses a helper column. See attached. There might be up to 6 ways to do this problem. DLL above used a text function of RIGHT to grab the right most character of each string in Col A (from row 2 to 1000) and tested it to be a zero.

[daddylonglegs]

RIGHT function returns the specified number of characters from the right of the cell value, if you don't specify the number of characters it defaults to 1 so if I have the number 345 in A2 then using

=RIGHT(A2)

will give me the result 5 as a text value

If I replace A2 with a range like A2:A10 then right function returns an array of values, showing the righthand character from each cell, so if A2:A10 has the numbers {345;112;32;5;650;12;23;45;0} then using the formula

=RIGHT(A2:A10)

...gives me this "array".....

={"5";"2";"2";"5";"0";"2";"3";"5";"0"}

[Note the quotes: that indicates that these are text values]

Now we can see how many of those are zeroes, so we use

=RIGHT(A2:A10)="0"

which then gives this array

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

where TRUEs occur where zeroes were.

To count the TRUEs I added zero to the above which converts TRUE to 1 and FALSE to 0, so

=(RIGHT(A2:A10)="0")+0

...gives you....

{0;0;0;0;1;0;0;0;1}

Now if I wrap a SUM function round that I can sum the 1s to give my answer 2.....but using SUM means that the formula is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER.....so I prefer SUMPRODUCT which doesn't need that complication, hence

=SUMPRODUCT((RIGHT(A2:A10)="0")+0)

of course you can extend A2:A10 to any range.....

[ruhat145]

RIGHT function returns the specified number of characters from the right of the cell value, if you don't specify the number of characters it defaults to 1 so if I have the number 345 in A2 then using

=RIGHT(A2)

will give me the result 5 as a text value

If I replace A2 with a range like A2:A10 then right function returns an array of values, showing the righthand character from each cell, so if A2:A10 has the numbers {345;112;32;5;650;12;23;45;0} then using the formula

=RIGHT(A2:A10)

...gives me this "array".....

={"5";"2";"2";"5";"0";"2";"3";"5";"0"}

[Note the quotes: that indicates that these are text values]

Now we can see how many of those are zeroes, so we use

=RIGHT(A2:A10)="0"

which then gives this array

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

where TRUEs occur where zeroes were.

To count the TRUEs I added zero to the above which converts TRUE to 1 and FALSE to 0, so

=(RIGHT(A2:A10)="0")+0

...gives you....

{0;0;0;0;1;0;0;0;1}

Now if I wrap a SUM function round that I can sum the 1s to give my answer 2.....but using SUM means that the formula is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER.....so I prefer SUMPRODUCT which doesn't need that complication, hence

=SUMPRODUCT((RIGHT(A2:A10)="0")+0)

of course you can extend A2:A10 to any range.....

Dude thank you very very very very much!!
very good explained!!

But i'm just curious how do you come up with this formula so fast?
Like how long have u been working with excel and do you recommend anything i can do to improve even more except for watching tutorials?
And if improving even more requires to watch many tutorials i'll use my time for that

Thaaanks !! :D

EDIT:
If you could help me with this one too would be awesome!!!
Count numbers ending with the digit 0
and beginning with the number 5.

I don't need any explanation for that cuz i'll probably figure it out myself.

[daddylonglegs]

Count numbers ending with the digit 0
and beginning with the number 5.

You can add more conditions to SUMPRODUCT, so that would be something like

=SUMPRODUCT((RIGHT(A2:A10)="0")+0,(LEFT(A2:A10)="5")+0)

[ruhat145]

You can add more conditions to SUMPRODUCT, so that would be something like

=SUMPRODUCT((RIGHT(A2:A10)="0")+0,(LEFT(A2:A10)="5")+0)

EDIT: It's fixed now thank you for your help.
I got 111 as result.