I am looking to add an AND, OR, or NOT to my formula or a combination of these
Right now I have
Col A Col b Col C
sumif(column A, "XXXX", column c) (column A meets X criteria then sum
col. c)
I want the formula to be if column a meets x criteria and the value in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't seem to put
it together in the right order.
Can you provide some guidance?
Thanks.

Try this:

=SUMPRODUCT(--(A1:A10="xxxx"),--(ISNUMBER(MATCH(B1:B10,{"y";"z"},0))),C1:C10)

Better to use cells to hold the criteria:

E1 = xxxx
F1 = y
G1 = z

=SUMPRODUCT(--(A1:A10=E1),--(ISNUMBER(MATCH(B1:B10,F1:G1,0))),C1:C10)

--
Biff
Microsoft Excel MVP


"JenniferCHW" wrote in message
...
I am looking to add an AND, OR, or NOT to my formula or a combination of
these
Right now I have
Col A Col b Col C
sumif(column A, "XXXX", column c) (column A meets X criteria then sum
col. c)
I want the formula to be if column a meets x criteria and the value in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't seem to
put
it together in the right order.
Can you provide some guidance?
Thanks.

Not sure if Y and Z are numbers or text - I'll assume numbers, but if
they are text you will need to put quotes around them. Try this:

=SUMPRODUCT((A1:A100="XXXX")*(B1:B100={Y,Z}),(C1:C 100))

Adjust the ranges to suit your data, but you can't use full-column
references if you are using Excel 2003 or earlier.

No need to model column B NOT being e, f, or g - these are excluded
anyway if B is Y or Z.

Hope this helps.

Pete

On Feb 4, 12:28*am, JenniferCHW
wrote:
I am looking to add an AND, OR, or NOT to my formula or a combination of these
Right now I have
Col A * * Col b * * Col C
sumif(column A, "XXXX", column c) * * (column A meets X criteria then sum
col. c)
I want the formula to be if column a meets x criteria and the value in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't seem to put
it together in the right order.
Can you provide some guidance?
Thanks.

I tried both responses and can't seem to get this to work. Let me be a bit
more specific with a short example.

Col A Col B Col C
Apple West 5
Orange South 7
Orange East 9
Orange West 4

I am looking for oranges in the west or east only. The answer is 13.


"Pete_UK" wrote:

Not sure if Y and Z are numbers or text - I'll assume numbers, but if
they are text you will need to put quotes around them. Try this:

=SUMPRODUCT((A1:A100="XXXX")*(B1:B100={Y,Z}),(C1:C 100))

Adjust the ranges to suit your data, but you can't use full-column
references if you are using Excel 2003 or earlier.

No need to model column B NOT being e, f, or g - these are excluded
anyway if B is Y or Z.

Hope this helps.

Pete

On Feb 4, 12:28 am, JenniferCHW
wrote:
I am looking to add an AND, OR, or NOT to my formula or a combination of these
Right now I have
Col A Col b Col C
sumif(column A, "XXXX", column c) (column A meets X criteria then sum
col. c)
I want the formula to be if column a meets x criteria and the value in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't seem to put
it together in the right order.
Can you provide some guidance?
Thanks.

E1 = orange
F1 = west
G1 = east

=SUMPRODUCT(--(A1:A4=E1),--(ISNUMBER(MATCH(B1:B4,F1:G1,0))),C1:C4)

Result = 13

--
Biff
Microsoft Excel MVP


"JenniferCHW" wrote in message
...
I tried both responses and can't seem to get this to work. Let me be a bit
more specific with a short example.

Col A Col B Col C
Apple West 5
Orange South 7
Orange East 9
Orange West 4

I am looking for oranges in the west or east only. The answer is 13.


"Pete_UK" wrote:

Not sure if Y and Z are numbers or text - I'll assume numbers, but if
they are text you will need to put quotes around them. Try this:

=SUMPRODUCT((A1:A100="XXXX")*(B1:B100={Y,Z}),(C1:C 100))

Adjust the ranges to suit your data, but you can't use full-column
references if you are using Excel 2003 or earlier.

No need to model column B NOT being e, f, or g - these are excluded
anyway if B is Y or Z.

Hope this helps.

Pete

On Feb 4, 12:28 am, JenniferCHW
wrote:
I am looking to add an AND, OR, or NOT to my formula or a combination
of these
Right now I have
Col A Col b Col C
sumif(column A, "XXXX", column c) (column A meets X criteria then
sum
col. c)
I want the formula to be if column a meets x criteria and the value in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't seem to
put
it together in the right order.
Can you provide some guidance?
Thanks.

One way:

=SUMPRODUCT(--(A1:A100="Orange"), (B1:B100="East")+(B1:B100="West"),
C1:C100)


In article ,
JenniferCHW wrote:

I tried both responses and can't seem to get this to work. Let me be a bit
more specific with a short example.

Col A Col B Col C
Apple West 5
Orange South 7
Orange East 9
Orange West 4

I am looking for oranges in the west or east only. The answer is 13.

This works. Please explain the purpose of the double minus signs. TIA.
hooroy


"T. Valko" wrote in message
...
E1 = orange
F1 = west
G1 = east

=SUMPRODUCT(--(A1:A4=E1),--(ISNUMBER(MATCH(B1:B4,F1:G1,0))),C1:C4)

Result = 13

--
Biff
Microsoft Excel MVP


"JenniferCHW" wrote in message
...
I tried both responses and can't seem to get this to work. Let me be a
bit
more specific with a short example.

Col A Col B Col C
Apple West 5
Orange South 7
Orange East 9
Orange West 4

I am looking for oranges in the west or east only. The answer is 13.


"Pete_UK" wrote:

Not sure if Y and Z are numbers or text - I'll assume numbers, but if
they are text you will need to put quotes around them. Try this:

=SUMPRODUCT((A1:A100="XXXX")*(B1:B100={Y,Z}),(C1:C 100))

Adjust the ranges to suit your data, but you can't use full-column
references if you are using Excel 2003 or earlier.

No need to model column B NOT being e, f, or g - these are excluded
anyway if B is Y or Z.

Hope this helps.

Pete

On Feb 4, 12:28 am, JenniferCHW
wrote:
I am looking to add an AND, OR, or NOT to my formula or a combination
of these
Right now I have
Col A Col b Col C
sumif(column A, "XXXX", column c) (column A meets X criteria then
sum
col. c)
I want the formula to be if column a meets x criteria and the value in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't seem
to put
it together in the right order.
Can you provide some guidance?
Thanks.

Each of these expressions will return an array of TRUE or FALSE:

(A1:A4=E1)
(ISNUMBER(MATCH(B1:B4,F1:G1,0)))

The double unary minus "--" coerces those logical values to 1 for TRUE and 0
for FALSE.

Then all 3 arrays are multiplied together:

0*1*5=0
1*0*7=0
1*1*9=9
1*1*4=4

Then summed for the final result:

=SUMPRODUCT({0;0;9;4}) = 13

For more info see:

http://xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Biff
Microsoft Excel MVP


"hooroy63" wrote in message
...
This works. Please explain the purpose of the double minus signs. TIA.
hooroy


"T. Valko" wrote in message
...
E1 = orange
F1 = west
G1 = east

=SUMPRODUCT(--(A1:A4=E1),--(ISNUMBER(MATCH(B1:B4,F1:G1,0))),C1:C4)

Result = 13

--
Biff
Microsoft Excel MVP


"JenniferCHW" wrote in message
...
I tried both responses and can't seem to get this to work. Let me be a
bit
more specific with a short example.

Col A Col B Col C
Apple West 5
Orange South 7
Orange East 9
Orange West 4

I am looking for oranges in the west or east only. The answer is 13.


"Pete_UK" wrote:

Not sure if Y and Z are numbers or text - I'll assume numbers, but if
they are text you will need to put quotes around them. Try this:

=SUMPRODUCT((A1:A100="XXXX")*(B1:B100={Y,Z}),(C1:C 100))

Adjust the ranges to suit your data, but you can't use full-column
references if you are using Excel 2003 or earlier.

No need to model column B NOT being e, f, or g - these are excluded
anyway if B is Y or Z.

Hope this helps.

Pete

On Feb 4, 12:28 am, JenniferCHW
wrote:
I am looking to add an AND, OR, or NOT to my formula or a combination
of these
Right now I have
Col A Col b Col C
sumif(column A, "XXXX", column c) (column A meets X criteria then
sum
col. c)
I want the formula to be if column a meets x criteria and the value
in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't seem
to put
it together in the right order.
Can you provide some guidance?
Thanks.

Thanks for the clear explanation.
hooroy

"T. Valko" wrote in message
...
Each of these expressions will return an array of TRUE or FALSE:

(A1:A4=E1)
(ISNUMBER(MATCH(B1:B4,F1:G1,0)))

The double unary minus "--" coerces those logical values to 1 for TRUE and
0 for FALSE.

Then all 3 arrays are multiplied together:

0*1*5=0
1*0*7=0
1*1*9=9
1*1*4=4

Then summed for the final result:

=SUMPRODUCT({0;0;9;4}) = 13

For more info see:

http://xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Biff
Microsoft Excel MVP


"hooroy63" wrote in message
...
This works. Please explain the purpose of the double minus signs. TIA.
hooroy


"T. Valko" wrote in message
...
E1 = orange
F1 = west
G1 = east

=SUMPRODUCT(--(A1:A4=E1),--(ISNUMBER(MATCH(B1:B4,F1:G1,0))),C1:C4)

Result = 13

--
Biff
Microsoft Excel MVP


"JenniferCHW" wrote in message
...
I tried both responses and can't seem to get this to work. Let me be a
bit
more specific with a short example.

Col A Col B Col C
Apple West 5
Orange South 7
Orange East 9
Orange West 4

I am looking for oranges in the west or east only. The answer is 13.


"Pete_UK" wrote:

Not sure if Y and Z are numbers or text - I'll assume numbers, but if
they are text you will need to put quotes around them. Try this:

=SUMPRODUCT((A1:A100="XXXX")*(B1:B100={Y,Z}),(C1:C 100))

Adjust the ranges to suit your data, but you can't use full-column
references if you are using Excel 2003 or earlier.

No need to model column B NOT being e, f, or g - these are excluded
anyway if B is Y or Z.

Hope this helps.

Pete

On Feb 4, 12:28 am, JenniferCHW
wrote:
I am looking to add an AND, OR, or NOT to my formula or a
combination of these
Right now I have
Col A Col b Col C
sumif(column A, "XXXX", column c) (column A meets X criteria
then sum
col. c)
I want the formula to be if column a meets x criteria and the value
in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't seem
to put
it together in the right order.
Can you provide some guidance?
Thanks.

A question very similar but a little different

Col B = name (text field)
Col C = Last name (text field)
Col D = Expences (number)

I would like to SUM to A1 total expences of a person answering True on Col B
& Col C

Name will apear more then once on A & B

Thanks a milion


"T. Valko" wrote:

Each of these expressions will return an array of TRUE or FALSE:

(A1:A4=E1)
(ISNUMBER(MATCH(B1:B4,F1:G1,0)))

The double unary minus "--" coerces those logical values to 1 for TRUE and 0
for FALSE.

Then all 3 arrays are multiplied together:

0*1*5=0
1*0*7=0
1*1*9=9
1*1*4=4

Then summed for the final result:

=SUMPRODUCT({0;0;9;4}) = 13

For more info see:

http://xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Biff
Microsoft Excel MVP


"hooroy63" wrote in message
...
This works. Please explain the purpose of the double minus signs. TIA.
hooroy


"T. Valko" wrote in message
...
E1 = orange
F1 = west
G1 = east

=SUMPRODUCT(--(A1:A4=E1),--(ISNUMBER(MATCH(B1:B4,F1:G1,0))),C1:C4)

Result = 13

--
Biff
Microsoft Excel MVP


"JenniferCHW" wrote in message
...
I tried both responses and can't seem to get this to work. Let me be a
bit
more specific with a short example.

Col A Col B Col C
Apple West 5
Orange South 7
Orange East 9
Orange West 4

I am looking for oranges in the west or east only. The answer is 13.


"Pete_UK" wrote:

Not sure if Y and Z are numbers or text - I'll assume numbers, but if
they are text you will need to put quotes around them. Try this:

=SUMPRODUCT((A1:A100="XXXX")*(B1:B100={Y,Z}),(C1:C 100))

Adjust the ranges to suit your data, but you can't use full-column
references if you are using Excel 2003 or earlier.

No need to model column B NOT being e, f, or g - these are excluded
anyway if B is Y or Z.

Hope this helps.

Pete

On Feb 4, 12:28 am, JenniferCHW
wrote:
I am looking to add an AND, OR, or NOT to my formula or a combination
of these
Right now I have
Col A Col b Col C
sumif(column A, "XXXX", column c) (column A meets X criteria then
sum
col. c)
I want the formula to be if column a meets x criteria and the value
in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't seem
to put
it together in the right order.
Can you provide some guidance?
Thanks.

Try this:

=SUMPRODUCT(--(B1:B10="John"),--(C1:C10="Smith"),D1:D10)

Better to use cells to hold the criteria:

A2 = some first name = John
A3 = some last name = Smith

=SUMPRODUCT(--(B1:B10=A2),--(C1:C10=A3),D1:D10)

--
Biff
Microsoft Excel MVP


"-yuval" wrote in message
...
A question very similar but a little different

Col B = name (text field)
Col C = Last name (text field)
Col D = Expences (number)

I would like to SUM to A1 total expences of a person answering True on Col
B
& Col C

Name will apear more then once on A & B

Thanks a milion


"T. Valko" wrote:

Each of these expressions will return an array of TRUE or FALSE:

(A1:A4=E1)
(ISNUMBER(MATCH(B1:B4,F1:G1,0)))

The double unary minus "--" coerces those logical values to 1 for TRUE
and 0
for FALSE.

Then all 3 arrays are multiplied together:

0*1*5=0
1*0*7=0
1*1*9=9
1*1*4=4

Then summed for the final result:

=SUMPRODUCT({0;0;9;4}) = 13

For more info see:

http://xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Biff
Microsoft Excel MVP


"hooroy63" wrote in message
...
This works. Please explain the purpose of the double minus signs. TIA.
hooroy


"T. Valko" wrote in message
...
E1 = orange
F1 = west
G1 = east

=SUMPRODUCT(--(A1:A4=E1),--(ISNUMBER(MATCH(B1:B4,F1:G1,0))),C1:C4)

Result = 13

--
Biff
Microsoft Excel MVP


"JenniferCHW" wrote in message
...
I tried both responses and can't seem to get this to work. Let me be
a
bit
more specific with a short example.

Col A Col B Col C
Apple West 5
Orange South 7
Orange East 9
Orange West 4

I am looking for oranges in the west or east only. The answer is 13.


"Pete_UK" wrote:

Not sure if Y and Z are numbers or text - I'll assume numbers, but
if
they are text you will need to put quotes around them. Try this:

=SUMPRODUCT((A1:A100="XXXX")*(B1:B100={Y,Z}),(C1:C 100))

Adjust the ranges to suit your data, but you can't use full-column
references if you are using Excel 2003 or earlier.

No need to model column B NOT being e, f, or g - these are excluded
anyway if B is Y or Z.

Hope this helps.

Pete

On Feb 4, 12:28 am, JenniferCHW
wrote:
I am looking to add an AND, OR, or NOT to my formula or a
combination
of these
Right now I have
Col A Col b Col C
sumif(column A, "XXXX", column c) (column A meets X criteria
then
sum
col. c)
I want the formula to be if column a meets x criteria and the
value
in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't
seem
to put
it together in the right order.
Can you provide some guidance?
Thanks.

But what is F1:g1 supposed to be set to?

"T. Valko" wrote:

Each of these expressions will return an array of TRUE or FALSE:

(A1:A4=E1)
(ISNUMBER(MATCH(B1:B4,F1:G1,0)))

The double unary minus "--" coerces those logical values to 1 for TRUE and 0
for FALSE.

Then all 3 arrays are multiplied together:

0*1*5=0
1*0*7=0
1*1*9=9
1*1*4=4

Then summed for the final result:

=SUMPRODUCT({0;0;9;4}) = 13

For more info see:

http://xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Biff
Microsoft Excel MVP


"hooroy63" wrote in message
...
This works. Please explain the purpose of the double minus signs. TIA.
hooroy


"T. Valko" wrote in message
...
E1 = orange
F1 = west
G1 = east

=SUMPRODUCT(--(A1:A4=E1),--(ISNUMBER(MATCH(B1:B4,F1:G1,0))),C1:C4)

Result = 13

--
Biff
Microsoft Excel MVP


"JenniferCHW" wrote in message
...
I tried both responses and can't seem to get this to work. Let me be a
bit
more specific with a short example.

Col A Col B Col C
Apple West 5
Orange South 7
Orange East 9
Orange West 4

I am looking for oranges in the west or east only. The answer is 13.


"Pete_UK" wrote:

Not sure if Y and Z are numbers or text - I'll assume numbers, but if
they are text you will need to put quotes around them. Try this:

=SUMPRODUCT((A1:A100="XXXX")*(B1:B100={Y,Z}),(C1:C 100))

Adjust the ranges to suit your data, but you can't use full-column
references if you are using Excel 2003 or earlier.

No need to model column B NOT being e, f, or g - these are excluded
anyway if B is Y or Z.

Hope this helps.

Pete

On Feb 4, 12:28 am, JenniferCHW
wrote:
I am looking to add an AND, OR, or NOT to my formula or a combination
of these
Right now I have
Col A Col b Col C
sumif(column A, "XXXX", column c) (column A meets X criteria then
sum
col. c)
I want the formula to be if column a meets x criteria and the value
in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't seem
to put
it together in the right order.
Can you provide some guidance?
Thanks.

F1:G1 are your lookup values:

F1 = West
G1 = East

--
Biff
Microsoft Excel MVP


"JenniferCHW" wrote in message
...
But what is F1:g1 supposed to be set to?

"T. Valko" wrote:

Each of these expressions will return an array of TRUE or FALSE:

(A1:A4=E1)
(ISNUMBER(MATCH(B1:B4,F1:G1,0)))

The double unary minus "--" coerces those logical values to 1 for TRUE
and 0
for FALSE.

Then all 3 arrays are multiplied together:

0*1*5=0
1*0*7=0
1*1*9=9
1*1*4=4

Then summed for the final result:

=SUMPRODUCT({0;0;9;4}) = 13

For more info see:

http://xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Biff
Microsoft Excel MVP


"hooroy63" wrote in message
...
This works. Please explain the purpose of the double minus signs. TIA.
hooroy


"T. Valko" wrote in message
...
E1 = orange
F1 = west
G1 = east

=SUMPRODUCT(--(A1:A4=E1),--(ISNUMBER(MATCH(B1:B4,F1:G1,0))),C1:C4)

Result = 13

--
Biff
Microsoft Excel MVP


"JenniferCHW" wrote in message
...
I tried both responses and can't seem to get this to work. Let me be
a
bit
more specific with a short example.

Col A Col B Col C
Apple West 5
Orange South 7
Orange East 9
Orange West 4

I am looking for oranges in the west or east only. The answer is 13.


"Pete_UK" wrote:

Not sure if Y and Z are numbers or text - I'll assume numbers, but
if
they are text you will need to put quotes around them. Try this:

=SUMPRODUCT((A1:A100="XXXX")*(B1:B100={Y,Z}),(C1:C 100))

Adjust the ranges to suit your data, but you can't use full-column
references if you are using Excel 2003 or earlier.

No need to model column B NOT being e, f, or g - these are excluded
anyway if B is Y or Z.

Hope this helps.

Pete

On Feb 4, 12:28 am, JenniferCHW
wrote:
I am looking to add an AND, OR, or NOT to my formula or a
combination
of these
Right now I have
Col A Col b Col C
sumif(column A, "XXXX", column c) (column A meets X criteria
then
sum
col. c)
I want the formula to be if column a meets x criteria and the
value
in
column b is either Y or Z but not e, f, g then sum col c.
If tried a bunch of different options with AND and OR but can't
seem
to put
it together in the right order.
Can you provide some guidance?
Thanks.