SUMIF and AND/OR/NOT

[JenniferCHW]

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.

[Pete_UK]

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:C100))

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

[T. Valko]

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" <Jenni...@discussions.microsoft.com> wrote in message
news:0C8E8119-08DF-44FF...@microsoft.com...

[JenniferCHW]

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.

[T. Valko]

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" <Jenni...@discussions.microsoft.com> wrote in message

news:083EA36D-1260-467B...@microsoft.com...

[JE McGimpsey]

One way:

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

In article <083EA36D-1260-467B...@microsoft.com>,

[hooroy63]

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

"T. Valko" <biffi...@comcast.net> wrote in message
news:%23RADhmm...@TK2MSFTNGP04.phx.gbl...

[T. Valko]

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" <hoor...@gmail.com> wrote in message
news:uOhZscnh...@TK2MSFTNGP04.phx.gbl...

[hooroy63]

Thanks for the clear explanation.
hooroy

"T. Valko" <biffi...@comcast.net> wrote in message

news:u93KPxnh...@TK2MSFTNGP04.phx.gbl...

[-yuval]

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]

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" <-yu...@discussions.microsoft.com> wrote in message
news:1D984CA0-B0BC-498C...@microsoft.com...

[JenniferCHW]

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

[T. Valko]

F1:G1 are your lookup values:

F1 = West
G1 = East

--
Biff
Microsoft Excel MVP

"JenniferCHW" <Jenni...@discussions.microsoft.com> wrote in message

news:C44536C3-F61E-49F5...@microsoft.com...