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
|