how to check first 5 characters of a cell & then sum

[jonny]

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

 

[lolo]

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

 

[Mike H]

Try

=SUMPRODUCT(--(LEFT(A1:A19,6)="ght991"),(B1:B19))

Mike

 

[Mike H]

Hi,

Forgot to mention that ght991 is 6 characters not 5

Mike

 

[Ron Rosenfeld]

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

 

[jonny]

=SUMPRODUCT((LEFT(A1:A4,6)="ght991")*(B1:B4))

Note that if you are going to check for the presence of "ght991" you willneed
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