How to count last number
 

I have numbers in columns labeled a through d. I would like to be able to
count the last digits in the numbers. Any ideas would be greatly
appreciated.

A B C D 1 2 3 4 5
6
93 86 21 83 2
1


Terry

How to count last number
 

Hi Terry,

Assuming you data start on row 2 and you have headings on row 1, with the
number 'count' headings starting in E1, try:
=SUM(IF(MOD($A2:$D2,10)=E$1,1,))
as an array formula in E2, copied down/across as far as needed.

FWIW, array formulae are input with <Ctrl-Shift-Enter, instead of just
<Enter

Cheers

--
macropod
[MVP - Microsoft Word]


"Terry M" wrote in message
...
I have numbers in columns labeled a through d. I would like to be able to
count the last digits in the numbers. Any ideas would be greatly
appreciated.

A B C D 1 2 3 4 5
6
93 86 21 83 2
1


Terry

How to count last number
 

Minor enhancement to disregard empty cells:
=SUM(IF((MOD($A2:$D2,10)=E$1)*($A2:$D2<""),1,))

Cheers

--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
Hi Terry,

Assuming you data start on row 2 and you have headings on row 1, with the
number 'count' headings starting in E1, try:
=SUM(IF(MOD($A2:$D2,10)=E$1,1,))
as an array formula in E2, copied down/across as far as needed.

FWIW, array formulae are input with <Ctrl-Shift-Enter, instead of just
<Enter

Cheers

--
macropod
[MVP - Microsoft Word]


"Terry M" wrote in message
...
I have numbers in columns labeled a through d. I would like to be able
to
count the last digits in the numbers. Any ideas would be greatly
appreciated.

A B C D 1 2 3 4 5
6
93 86 21 83 2
1


Terry

How to count last number
 

Thanks, I'll give it a try and let you know. Thanks again.
Terry
macropod wrote:
Minor enhancement to disregard empty cells:
=SUM(IF((MOD($A2:$D2,10)=E$1)*($A2:$D2<""),1,))

Cheers

--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
Hi Terry,

Assuming you data start on row 2 and you have headings on row 1, with the
number 'count' headings starting in E1, try:
=SUM(IF(MOD($A2:$D2,10)=E$1,1,))
as an array formula in E2, copied down/across as far as needed.

FWIW, array formulae are input with <Ctrl-Shift-Enter, instead of just
<Enter

Cheers

--
macropod
[MVP - Microsoft Word]


"Terry M" wrote in message
...
I have numbers in columns labeled a through d. I would like to be able
to
count the last digits in the numbers. Any ideas would be greatly
appreciated.

A B C D 1 2 3 4 5
6
93 86 21 83 2
1


Terry

How to count last number
 

It didn't work, I kept getting a value error. It seemed ok when I clicked on
the fx button to evaluate the formula, but not on the sheet.

"macropod" wrote in message
...
Minor enhancement to disregard empty cells:
=SUM(IF((MOD($A2:$D2,10)=E$1)*($A2:$D2<""),1,))

Cheers

--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
Hi Terry,

Assuming you data start on row 2 and you have headings on row 1, with the
number 'count' headings starting in E1, try:
=SUM(IF(MOD($A2:$D2,10)=E$1,1,))
as an array formula in E2, copied down/across as far as needed.

FWIW, array formulae are input with <Ctrl-Shift-Enter, instead of just
<Enter

Cheers

--
macropod
[MVP - Microsoft Word]


"Terry M" wrote in message
...
I have numbers in columns labeled a through d. I would like to be able
to
count the last digits in the numbers. Any ideas would be greatly
appreciated.

A B C D 1 2 3 4 5
6
93 86 21 83 2
1


Terry

How to count last number
 

I found a way to do it. I took your formula and put a -- in from of the
MOD. I tried it in individual cells and it worked great, haven't tried it as
an array formula yet. Thanks, your help was greatly appreciated.

Terry

"Tmaxx02" wrote in message
ups.com...
Thanks, I'll give it a try and let you know. Thanks again.
Terry
macropod wrote:
Minor enhancement to disregard empty cells:
=SUM(IF((MOD($A2:$D2,10)=E$1)*($A2:$D2<""),1,))

Cheers

--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
Hi Terry,

Assuming you data start on row 2 and you have headings on row 1, with
the
number 'count' headings starting in E1, try:
=SUM(IF(MOD($A2:$D2,10)=E$1,1,))
as an array formula in E2, copied down/across as far as needed.

FWIW, array formulae are input with <Ctrl-Shift-Enter, instead of just
<Enter

Cheers

--
macropod
[MVP - Microsoft Word]


"Terry M" wrote in message
...
I have numbers in columns labeled a through d. I would like to be
able
to
count the last digits in the numbers. Any ideas would be greatly
appreciated.

A B C D 1 2 3 4
5
6
93 86 21 83 2
1


Terry

How to count last number
 

Hi Terry,

Putting '--' before MOD in the formula won't make any difference - it will
return the correct result regardless.

Putting '--' before E$1 in the formula will make a difference, but only if
the 'lookup' value on row 1 has been input as text instead of as a number.

Either way, the formula won't work at all unless you input it as an array
formula.

Cheers

--
macropod
[MVP - Microsoft Word]


"Terry M" wrote in message
...
I found a way to do it. I took your formula and put a -- in from of the
MOD. I tried it in individual cells and it worked great, haven't tried it
as
an array formula yet. Thanks, your help was greatly appreciated.

Terry

"Tmaxx02" wrote in message
ups.com...
Thanks, I'll give it a try and let you know. Thanks again.
Terry
macropod wrote:
Minor enhancement to disregard empty cells:
=SUM(IF((MOD($A2:$D2,10)=E$1)*($A2:$D2<""),1,))

Cheers

--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
Hi Terry,

Assuming you data start on row 2 and you have headings on row 1, with
the
number 'count' headings starting in E1, try:
=SUM(IF(MOD($A2:$D2,10)=E$1,1,))
as an array formula in E2, copied down/across as far as needed.

FWIW, array formulae are input with <Ctrl-Shift-Enter, instead of
just
<Enter

Cheers

--
macropod
[MVP - Microsoft Word]


"Terry M" wrote in message
...
I have numbers in columns labeled a through d. I would like to be
able
to
count the last digits in the numbers. Any ideas would be greatly
appreciated.

A B C D 1 2 3 4
5
6
93 86 21 83 2
1


Terry

How to count last number
 

I don't know much about it, but I modified it to work on one cell. Either
way, I got it to work the way I want it to. Thanks very much.

"macropod" wrote in message
...
Hi Terry,

Putting '--' before MOD in the formula won't make any difference - it will
return the correct result regardless.

Putting '--' before E$1 in the formula will make a difference, but only if
the 'lookup' value on row 1 has been input as text instead of as a number.

Either way, the formula won't work at all unless you input it as an array
formula.

Cheers

--
macropod
[MVP - Microsoft Word]


"Terry M" wrote in message
...
I found a way to do it. I took your formula and put a -- in from of the
MOD. I tried it in individual cells and it worked great, haven't tried it
as
an array formula yet. Thanks, your help was greatly appreciated.

Terry

"Tmaxx02" wrote in message
ups.com...
Thanks, I'll give it a try and let you know. Thanks again.
Terry
macropod wrote:
Minor enhancement to disregard empty cells:
=SUM(IF((MOD($A2:$D2,10)=E$1)*($A2:$D2<""),1,))

Cheers

--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
Hi Terry,

Assuming you data start on row 2 and you have headings on row 1,
with
the
number 'count' headings starting in E1, try:
=SUM(IF(MOD($A2:$D2,10)=E$1,1,))
as an array formula in E2, copied down/across as far as needed.

FWIW, array formulae are input with <Ctrl-Shift-Enter, instead of
just
<Enter

Cheers

--
macropod
[MVP - Microsoft Word]


"Terry M" wrote in message
...
I have numbers in columns labeled a through d. I would like to be
able
to
count the last digits in the numbers. Any ideas would be greatly
appreciated.

A B C D 1 2 3 4
5
6
93 86 21 83 2
1


Terry