It works great ! Took me some experimenting, but finally got it working with
=sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E$6 = "H")).
The article on the website you referred to is very helpful; have to read
thru the whole thing at some point still.
This take me to the next step and seem now to have problem with trying to
match certain key word in the target cell by using wildcard charter.
So, I have to do the same process with 2 different source of data. In first
case, got it working with the above formula where the 2nd criteria is
comparing to a fixed value. However, with the 2nd data source, I need to
find records that contain a certain key word from a longer string of data in
the cell. So, same example of table as before, but Col E now contains free
from text description of the request. And it may start with the word RUSH if
it is urgent. And that word could appear in variety of formate such as (RUSH,
Rush, rush, **** Rush ***, and so on). So I coded the formula as :
=sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E$6 = "*Rush")). Yet it
won't pick up the records.
Strange thing is that I tried doing a countif with the same wildcard
expression and it works.
=COUNTIF(TableA!($E1$1:$E$6,"*Rush*") And it can find those records.
So, wonder if there is something about using '*' as wildcard charter with
SUMPRODUCT ? And how do I get around this ?
"Jim Thomlinson" wrote: