How to count last number

[Terry M]

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

 

[macropod]

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]

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

Cheers

--
macropod
[MVP - Microsoft Word]

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]

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

 

[Tmaxx02]

Thanks, I'll give it a try and let you know. Thanks again.

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

Cheers

--
macropod
[MVP - Microsoft Word]

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]

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

 

[Terry M]

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.

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

Cheers

--
macropod
[MVP - Microsoft Word]

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]

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

 

[Terry M]

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

Thanks, I'll give it a try and let you know. Thanks again.

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

Cheers

--
macropod
[MVP - Microsoft Word]

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]


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

 

[macropod]

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]

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

Thanks, I'll give it a try and let you know. Thanks again.

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

Cheers

--
macropod
[MVP - Microsoft Word]


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]


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

 

[Terry M]

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.

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]

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

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]


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]


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