Count numbers [HELP]

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

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

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

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.

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.....

Originally Posted by 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.....

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.

Originally Posted by ruhat145

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)

Originally Posted by daddylonglegs

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.