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

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

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
one has the word "blue" AND column C matches ANY value in z1:z4 list, return
the number of units.

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...

"Teethless mama" wrote:

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

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))),D2:D10)


--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
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
one has the word "blue" AND column C matches ANY value in z1:z4 list,
return
the number of units.

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...

"Teethless mama" wrote:

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

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.


"T. Valko" wrote:

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))),D2:D10)


--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
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
one has the word "blue" AND column C matches ANY value in z1:z4 list,
return
the number of units.

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...

"Teethless mama" wrote:

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

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" wrote:

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))),D2:D10)


--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
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
one has the word "blue" AND column C matches ANY value in z1:z4 list,
return
the number of units.

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...

"Teethless mama" wrote:

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

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?

--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
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" wrote:

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))),D2:D10)


--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
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
one has the word "blue" AND column C matches ANY value in z1:z4
list,
return
the number of units.

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...

"Teethless mama" wrote:

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