=SUMPRODUCT(--(C1:C10<"Brand
A"),--(ISNUMBER(MATCH(B1:B10,G5:G6,0))),A1:A10)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"merry_fay" wrote in message
...
Hi,

I've looked at this formula & can get it to work for my data if I type in
the criteria eg {"France","Italy"}, but what I really need is to be able
to
use a cell reference eg {G5,G6} instead. How can I change the formula to
do
this?

Thanks
Miranda

"Bob Phillips" wrote:

=SUMPRODUCT(--(C1:C10<"Brand
A"),--(ISNUMBER(MATCH(B1:B10,{"France","Italy"},0))),A1: A10)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Inter" wrote in message
...
Thanks for both of these ... very helpful.

Follow up question though - how about if i want to sum the cells in
column
A
that have "France" OR "Italy" in column B but DO NOT have "Brand A"
(i.e.
do
have anything apart from "Brand A") in column C?

Cheers
Stuart