Answere first part:

=SUMPRODUCT(((A2:A10="Blue")+(B2:B10="Blue")0)*(C 2:C10=Z1),D2:D10)

Answere second part:

=SUMPRODUCT(((ISNUMBER(MATCH(A2:A10,Y1:Y2,0)))+(IS NUMBER(MATCH(B2:B10,Y1:Y2,0)))0)*(ISNUMBER(MATCH( C2:C10,Z1:Z4,0))),D2:D10)


"Tami" wrote:

i have data that looks like this in column A1:D10 and a list in Z1:Z4 and
Y1:y2

A B C D
1 color 1 color 2 theme units
2 Blue red heart 10
3 Blue none none 12
4 blue pink heart 18
5 Pink none none 11
6 Pink none heart 16
7 pink blue dot 15
8 red blue heart 12
9 red pink dot 10
10 red none dot 5


Z
1 heart
2 dot
3 square
4 stripe


y
1 blue
2 red


My question has two parts:

first, i need a sumproduct formula that scans column A and B and if EITHERr
one has the word "blue" AND column C matches a value in z1:z4 list, return
the number of units.

So for BLue/heart the formula would return 40 units.

2nd part.

the formula will look at list in Y1:y2, go find a match in columns A or B,
scan column C and find a match in z:1:4 list, then sum the units. but don't
double count if Blue is in both A and B.
so the answer would be 70


hope that makes sense and hope someone can help.
thx much
tami