Prev: Find cell value within table
Next: Excel Problem
From: Rick Rothstein on 24 Feb 2010 12:31
My previous post gave you the method to SUMIF for an exact match... if you
need to do a partial match, then just use the asterisk wildcard on either
side of the text you are searching for...

=SUMIF(A1:A1000,"*partial match me*",B1:B1000)

Note the asterisks on either end of the to-be-searched-for string... the
above will sum up the values in Column B for any cell in Column A that
contains the text "partial match me" either in whole or within a larger text
string containing that text.

--
Rick (MVP - Excel)


"Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message
news:OG6AYRXtKHA.1796(a)TK2MSFTNGP02.phx.gbl...
> If I understand what you are trying to do, I think the SUMIF function is
> what you will want to use. Assuming Column A has your strings to be
> searched and Column B has your numbers to be summed (just change the
> ranges as required; you can specify a larger range than your current data
> set if you will adding new data in the future)...
>
> =SUMIF(A1:A1000,"Some string value",B1:B1000)
>
> --
> Rick (MVP - Excel)
>
>
> "BG Mark" <BGMark(a)discussions.microsoft.com> wrote in message
> news:767C1228-9339-46AA-8CDD-CD7A48489BB4(a)microsoft.com...
>> How can I use the Find function within an array formula to check a column
>> to
>> locate evey occurence of a string and subsequently sum the corresponding
>> numbers in a different column
>

From: Luke M on 24 Feb 2010 12:52
As Rick points out, SUMIF can use wildcards (something I had forgotten),
thus his solution is better.

--
Best Regards,

Luke M
"Luke M" <lukemoraga(a)nospam.com> wrote in message
news:%23ZxFSQXtKHA.1796(a)TK2MSFTNGP02.phx.gbl...
> Something like this:
>
> =SUMPRODUCT(--(ISNUMBER(FIND("MyString",A2:A100))),B2:B100)
>
> or the non-case sensitive version:
>
> =SUMPRODUCT(--(ISNUMBER(SEARCH("MyString",A2:A100))),B2:B100)
>
> --
> Best Regards,
>
> Luke M
> "BG Mark" <BGMark(a)discussions.microsoft.com> wrote in message
> news:767C1228-9339-46AA-8CDD-CD7A48489BB4(a)microsoft.com...
>> How can I use the Find function within an array formula to check a column
>> to
>> locate evey occurence of a string and subsequently sum the corresponding
>> numbers in a different column
>
>


First  |  Prev  | 
Pages: 1 2
Prev: Find cell value within table
Next: Excel Problem