The formula I suggested was based on your post and my understanding that you
seemed to want a sum if cells contained one of several words and the samples
you posted contained more than one of those words.
If you're only searching for a single word:
I needed it to reference a cell
Then we can simplify things greatly:
=SUMPRODUCT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),B$2:B$5462)
Or, even:
=SUMIF(A2:5462,"*"&D3&"*",B2:B5462)
Does it limit the lookup to 5462 rows
The MMULT function is limited to 5461 rows. The version above is not limited
except that you can't use entire columns as range references unless you're
using Excel 2007.
MMULT = matrix multiplication
I would love to know what all this is doing. In
particular the (--MMULT(--(ISNUMBER part of the formula.
It's kind of hard to explain but here's an explanation I wrote a while back
for someone else. The logic is the same but this example was for a slightly
different request.
==========
For an explanation lets use a very small sample:
......A.....B.....C
1...1......5.....10
You want to count how many times 5 and 10 appear on the same row.
A10 = 5
B10 = 10
=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2))
This portion of the formula tests to see if any numbers in A1:C1 match the
numbers in A10:B10
ISNUMBER(MATCH(A1:C1,A10:B10,0))
This will return a horizontal array of either TRUE or FALSE
FALSE TRUE TRUE
We need to convert those logical values to numbers. To do that we use the
double unary:
--(ISNUMBER(MATCH(A1:C1,A10:B10,0))
That will convert TRUE to 1 and FALSE to 0:
0 1 1
MMULT (matrix multiplication) is then used to return the count of matches
per row. We multiply the horizontal array
0 1 1 by a vertical array equal to the number of columns in the data set.
In this case we have 3 columns A1:C1, so the vertical array is {1;1;1}
It would look something like this:
0..1..1.......1
..................1
..................1
The 0 times the top vertical 1
The middle horizontal 1 times the middle vertical 1
The rightmost horizontal 1 times the bottom vertical 1
The result would be:
0*1 + 1*1 + 1*1
0 + 1 + 1 = 2
Now, imagine your sample with 3 rows of data. The MMULT function would
return a count like that above for each row. Those counts are then passed to
the SUMPRODUCT function where they are tested to see if they equal 2 (for 2
matches: A1:C1 matches A10 and A1:C1 matches B10).
This is also an array of TRUE or FALSE. Then we once again convert those
logical values to 1 and 0 then the SUMPRODUCT totals those to arrive at the
final result of 1 (based on this explanation sample).
==========
