Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to check first 5 characters of a cell & then sum
Hi,
I'm looking for a way to check the first 5 characters of a cell for a certain value and if it equals that value to sum the value of another cell e.g. Column A, B, ght991xyz, 10 ght887fht, 100 ght991xyz, 50 ght887fht, 7 So in the example above I'd want to check all rows where column A starts with ght991 and then sums the value of column B. So the result of the above would be 60 (50+10).. Not that column A will always contain digits after the first five, but only the first 5 will match. Any help would be greatly appreciated. Best Regards |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to check first 5 characters of a cell & then sum
On 20 Sty, 12:52, jonny wrote:
Hi, I'm looking for a way to check the first 5 characters of a cell for a certain value and if it equals that value to sum the value of another cell e.g. Column A, B, ght991xyz, 10 ght887fht, 100 ght991xyz, 50 ght887fht, 7 So in the example above I'd want to check all rows where column A starts with ght991 and then sums the value of column B. *So the result of the above would be 60 (50+10).. Not that column A will always contain digits after the first five, but only the first 5 will match. Any help would be greatly appreciated. Best Regards The easisest thing that comes to my mind: Make a column C, put this formula in it: =IF((MID(A2;1;6)="ght991");B2;0) It displays the column B number, if the first 6 chars of column A are ght991, otherwise zero. Then you just have to sum all of column C and you get the result. Regards lolo |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to check first 5 characters of a cell & then sum
Try
=SUMPRODUCT(--(LEFT(A1:A19,6)="ght991"),(B1:B19)) Mike "jonny" wrote: Hi, I'm looking for a way to check the first 5 characters of a cell for a certain value and if it equals that value to sum the value of another cell e.g. Column A, B, ght991xyz, 10 ght887fht, 100 ght991xyz, 50 ght887fht, 7 So in the example above I'd want to check all rows where column A starts with ght991 and then sums the value of column B. So the result of the above would be 60 (50+10).. Not that column A will always contain digits after the first five, but only the first 5 will match. Any help would be greatly appreciated. Best Regards |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to check first 5 characters of a cell & then sum
Hi,
Forgot to mention that ght991 is 6 characters not 5 Mike "Mike H" wrote: Try =SUMPRODUCT(--(LEFT(A1:A19,6)="ght991"),(B1:B19)) Mike "jonny" wrote: Hi, I'm looking for a way to check the first 5 characters of a cell for a certain value and if it equals that value to sum the value of another cell e.g. Column A, B, ght991xyz, 10 ght887fht, 100 ght991xyz, 50 ght887fht, 7 So in the example above I'd want to check all rows where column A starts with ght991 and then sums the value of column B. So the result of the above would be 60 (50+10).. Not that column A will always contain digits after the first five, but only the first 5 will match. Any help would be greatly appreciated. Best Regards |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to check first 5 characters of a cell & then sum
On Tue, 20 Jan 2009 03:52:22 -0800 (PST), jonny wrote:
Hi, I'm looking for a way to check the first 5 characters of a cell for a certain value and if it equals that value to sum the value of another cell e.g. Column A, B, ght991xyz, 10 ght887fht, 100 ght991xyz, 50 ght887fht, 7 So in the example above I'd want to check all rows where column A starts with ght991 and then sums the value of column B. So the result of the above would be 60 (50+10).. Not that column A will always contain digits after the first five, but only the first 5 will match. Any help would be greatly appreciated. Best Regards =SUMPRODUCT((LEFT(A1:A4,6)="ght991")*(B1:B4)) Note that if you are going to check for the presence of "ght991" you will need to check the first *SIX* characters, and not just FIVE. --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to check first 5 characters of a cell & then sum
On 20 Jan, 12:44, Ron Rosenfeld wrote:
On Tue, 20 Jan 2009 03:52:22 -0800 (PST), jonny wrote: Hi, I'm looking for a way to check the first 5 characters of a cell for a certain value and if it equals that value to sum the value of another cell e.g. Column A, B, ght991xyz, 10 ght887fht, 100 ght991xyz, 50 ght887fht, 7 So in the example above I'd want to check all rows where column A starts with ght991 and then sums the value of column B. *So the result of the above would be 60 (50+10).. Not that column A will always contain digits after the first five, but only the first 5 will match. Any help would be greatly appreciated. Best Regards =SUMPRODUCT((LEFT(A1:A4,6)="ght991")*(B1:B4)) Note that if you are going to check for the presence of "ght991" you will need to check the first *SIX* characters, and not just FIVE. --ron- Hide quoted text - - Show quoted text - hi everyone, thanks for your help with this.. The solution works a treat!! By the way you all passed my test, it was indeed 6 characters and not 5! haha.. Thanks again, Jon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot check the check mark box in cell | Excel Worksheet Functions | |||
Characters in cell | Excel Discussion (Misc queries) | |||
Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions | |||
Transfer specific characters from cell to another cell. | New Users to Excel | |||
Check for Alpha characters | Excel Discussion (Misc queries) |