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


.