test if a logical statement and use the answer within an if statem
in [Functions]
|
Prev: Find cell value within table
Next: Excel Problem
From: BG Mark on 24 Feb 2010 11:55 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:09 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
From: Rick Rothstein on 24 Feb 2010 12:11 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: Eduardo on 24 Feb 2010 12:20 Hi, I assume you look for letter a so try, enter formula as an array Ctrl + Shift + Enter , it will put {} at the beginning and at the end =SUM(IF(A8:A11="a",B8:B11)) "BG Mark" wrote: > 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: Gary''s Student on 24 Feb 2010 12:24
Say we are searching for "happiness" in column A and want to sum the corresponding values in column B: =SUMPRODUCT(--(LEN(SUBSTITUTE(A1:A100,"happiness",""))<>LEN(A1:A100))*(B1:B100)) so: happiness is good 1 happiness is over-rated 13 sadness should be avoided 37 will return a 14 -- Gary''s Student - gsnu201001 "BG Mark" wrote: > 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 |