Sumproduct - meet multiple criteria from a list
 

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

Sumproduct - meet multiple criteria from a list
 

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

Sumproduct - meet multiple criteria from a list
 

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

Sumproduct - meet multiple criteria from a list
 

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

Sumproduct - meet multiple criteria from a list
 

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

Sumproduct - meet multiple criteria from a list
 

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

Sumproduct - meet multiple criteria from a list
 

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