Sumproduct - meet multiple criteria from a list

[Tami]

i have data that looks like this in column A110 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

 

[Teethless mama]

Answere first part:

=SUMPRODUCT(((A2:A10="Blue")+(B2:B10="Blue")>0)*(C2:C10=Z1),D210)

Answere second part:

=SUMPRODUCT(((ISNUMBER(MATCH(A2:A10,Y1:Y2,0)))+(ISNUMBER(MATCH(B2:B10,Y1:Y2,0)))>0)*(ISNUMBER(MATCH(C2:C10,Z1:Z4,0))),D210)

 

[Tami]

2nd part worked beautifully, thank you Teethless mama.
Unfortunately i mis-asked my first queston

yes, your formula answeres Blue heart but i meant to ask

i need a sumproduct formula that scans column A and B and if EITHERr
so i should have asked blue heart, blue dot, blue square, blue stripe = 55

am i making sense?
i'm going to google isnumber & match... so i can better understand why your
2nd formula worked...

 

[T. Valko]

so i should have asked blue heart, blue dot,

blue square, blue stripe = 55

Try this:

F2 = Blue

=SUMPRODUCT(SIGN((A2:A10=F2)+(B2:B10=F2)),--(ISNUMBER(MATCH(C2:C10,Z1:Z4,0))),D210)

 

[Tami]

yep, this worked perfectly for my sample data below
but, when i put it in my model, it doesn't work. I do have some blank lines
in my real data (the a:d part), possibly a word in column D, but definitely
no n/a's or ref's.

 

[Tami]

nevermind t.valko! it worked!....i changed the range to a small range with
no wierd cells/blanks etc and it worked, then i slowly increased the range
trying to identify what cell was wacking it out and it never did....so it
worked.

final question related to this schedule...
what if i wanted to Count the occurences...like this table

for example

ROW Colum Q Colum R Colum S

1 blue red
2 heart 3 2
3 dot 1 2
4 square 0 0
5 stripe 0 0

 

[T. Valko]

what if i wanted to Count the occurences...like this table

1 blue red
2 heart 3 2
3 dot 1 2
4 square 0 0
5 stripe 0 0

What exactly do you want to count?