test if a logical statement and use the answer within an if statem

  • Thread starter Thread starter BG Mark
  • Start date Start date
B

BG Mark

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)
 
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)
 
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))
 
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
 
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.
 
As Rick points out, SUMIF can use wildcards (something I had forgotten),
thus his solution is better.
 
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.
 
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))*((B1: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).
 
Thank your Rick, that appears to work just fine.

Rick Rothstein said:
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))*((B1: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)




.
Click to expand...
 
Back
Top