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

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" wrote in message
...
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

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" wrote in message
...
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

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

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:A 100))*(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

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" wrote in message
...
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" wrote in message
...
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

As Rick points out, SUMIF can use wildcards (something I had forgotten),
thus his solution is better.

--
Best Regards,

Luke M
"Luke M" wrote in message
...
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" wrote in message
...
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

Firstly thanks to all who replied, but I have an additional problem.

Column A has the data that I am searching for, and the SUMIF would work
ideally if all the corresponding values in column be were of the same format,
however column B will contain either 1, A, D or blank and I need also to add
1 to the total for every occurence of value A.

eg
Column A Column B
Mech Shift 1 1
Inst Shift 1 1
Elec Shift 1 1
Mech Shift 2 A
Mech Shift 3 D

I need a formula that will serch for every occurence of Mech in column A and
count only the 1's and A's in column B, so in the above case the total should
be 2.
In case anyone is wondering, A and D stand for arrival and departure.

"Rick Rothstein" wrote:

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" wrote in message
...
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" wrote in message
...
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


.

That is a completely different problem than you first posted and, of course,
it requires a completely different solution. See if this does what you
want...

=SUMPRODUCT((ISNUMBER(SEARCH("mech",A1:A1000))*((B 1:B1000=1)+(B1:B1000="A"))))

Change the range limits as needed. You can specify a larger range than your
current data set if you will adding new data in the future, but note that
the row limits for each range in the above formula must be the same (that
is, start at the same row number and end with the same row number).

--
Rick (MVP - Excel)


"BG Mark" wrote in message
...
Firstly thanks to all who replied, but I have an additional problem.

Column A has the data that I am searching for, and the SUMIF would work
ideally if all the corresponding values in column be were of the same
format,
however column B will contain either 1, A, D or blank and I need also to
add
1 to the total for every occurence of value A.

eg
Column A Column B
Mech Shift 1 1
Inst Shift 1 1
Elec Shift 1 1
Mech Shift 2 A
Mech Shift 3 D

I need a formula that will serch for every occurence of Mech in column A
and
count only the 1's and A's in column B, so in the above case the total
should
be 2.
In case anyone is wondering, A and D stand for arrival and departure.

"Rick Rothstein" wrote:

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" wrote in message
...
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" wrote in message
...
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


.

Thank your Rick, that appears to work just fine.

"Rick Rothstein" wrote:

That is a completely different problem than you first posted and, of course,
it requires a completely different solution. See if this does what you
want...

=SUMPRODUCT((ISNUMBER(SEARCH("mech",A1:A1000))*((B 1:B1000=1)+(B1:B1000="A"))))

Change the range limits as needed. You can specify a larger range than your
current data set if you will adding new data in the future, but note that
the row limits for each range in the above formula must be the same (that
is, start at the same row number and end with the same row number).

--
Rick (MVP - Excel)


"BG Mark" wrote in message
...
Firstly thanks to all who replied, but I have an additional problem.

Column A has the data that I am searching for, and the SUMIF would work
ideally if all the corresponding values in column be were of the same
format,
however column B will contain either 1, A, D or blank and I need also to
add
1 to the total for every occurence of value A.

eg
Column A Column B
Mech Shift 1 1
Inst Shift 1 1
Elec Shift 1 1
Mech Shift 2 A
Mech Shift 3 D

I need a formula that will serch for every occurence of Mech in column A
and
count only the 1's and A's in column B, so in the above case the total
should
be 2.
In case anyone is wondering, A and D stand for arrival and departure.

"Rick Rothstein" wrote:

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" wrote in message
...
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" wrote in message
...
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


.


.