Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I count the number of times text in column A matches text i | Excel Worksheet Functions | |||
Number count - postcodes and need to count the numbers of tim... | Excel Discussion (Misc queries) | |||
How to count the number of times something occurs within a certain month | Excel Worksheet Functions | |||
Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions | |||
Need to Count number of occurrences and get percentage of total | Excel Worksheet Functions |