Multiple Criterion in a SUMIF function
 

How would I incorporate 2 criterion into the SUMIF function? For example, in
the following example I have the formula look to the A2:A2000 range for the
value referrenced in C2 and sum only those numbers in range B2:B2000 that
meet this criteria. That's the easy part. How would I add another layer of
criteria to the formula below in addition to that specified by "C2" - say,
EXACT(E2,VLOOKUP(E1,A2:B2000,2,FALSE))=TRUE?

=SUMIF(B2:B2000,E2,C2:C2000)

thx.
--
iperlovsky

Multiple Criterion in a SUMIF function
 

=SUMPRODUCT(--((A2:A200=C2)+(A2:A200=E2))*B2:B200)


"IPerlovsky" wrote:

How would I incorporate 2 criterion into the SUMIF function? For example, in
the following example I have the formula look to the A2:A2000 range for the
value referrenced in C2 and sum only those numbers in range B2:B2000 that
meet this criteria. That's the easy part. How would I add another layer of
criteria to the formula below in addition to that specified by "C2" - say,
EXACT(E2,VLOOKUP(E1,A2:B2000,2,FALSE))=TRUE?

=SUMIF(B2:B2000,E2,C2:C2000)

thx.
--
iperlovsky

Multiple Criterion in a SUMIF function
 

=SUMPRODUCT(--(B2:B2000=E2),--(EXACT(E2:E2000,VLOOKUP(E1,A2:B2000,2,FALSE))),C2: C2000)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"IPerlovsky" wrote in message
...
How would I incorporate 2 criterion into the SUMIF function? For example,
in
the following example I have the formula look to the A2:A2000 range for
the
value referrenced in C2 and sum only those numbers in range B2:B2000 that
meet this criteria. That's the easy part. How would I add another layer
of
criteria to the formula below in addition to that specified by "C2" - say,
EXACT(E2,VLOOKUP(E1,A2:B2000,2,FALSE))=TRUE?

=SUMIF(B2:B2000,E2,C2:C2000)

thx.
--
iperlovsky

Multiple Criterion in a SUMIF function
 

Is this formula not multiplying? I am trying only to sum a data set that
meets 2 conditions.

--
iperlovsky


"Teethless mama" wrote:

=SUMPRODUCT(--((A2:A200=C2)+(A2:A200=E2))*B2:B200)


"IPerlovsky" wrote:

How would I incorporate 2 criterion into the SUMIF function? For example, in
the following example I have the formula look to the A2:A2000 range for the
value referrenced in C2 and sum only those numbers in range B2:B2000 that
meet this criteria. That's the easy part. How would I add another layer of
criteria to the formula below in addition to that specified by "C2" - say,
EXACT(E2,VLOOKUP(E1,A2:B2000,2,FALSE))=TRUE?

=SUMIF(B2:B2000,E2,C2:C2000)

thx.
--
iperlovsky

Multiple Criterion in a SUMIF function
 

Thank you. I modified it slightly for simplicity as:

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000)

How would I employ that methodoligy in the following 2 formulas so that it
would:
countif, based on 2 criterion...

=COUNTIF(--((J2:J2000=E8),--(K2:K2000=E9)),E8)

and max(if, based on 2 criterion...

=MAX(IF(--((J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)))

thx..

--
iperlovsky


"Bob Phillips" wrote:

=SUMPRODUCT(--(B2:B2000=E2),--(EXACT(E2:E2000,VLOOKUP(E1,A2:B2000,2,FALSE))),C2: C2000)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"IPerlovsky" wrote in message
...
How would I incorporate 2 criterion into the SUMIF function? For example,
in
the following example I have the formula look to the A2:A2000 range for
the
value referrenced in C2 and sum only those numbers in range B2:B2000 that
meet this criteria. That's the easy part. How would I add another layer
of
criteria to the formula below in addition to that specified by "C2" - say,
EXACT(E2,VLOOKUP(E1,A2:B2000,2,FALSE))=TRUE?

=SUMIF(B2:B2000,E2,C2:C2000)

thx.
--
iperlovsky

Multiple Criterion in a SUMIF function
 

"IPerlovsky" wrote in message
...
Thank you. I modified it slightly for simplicity as:

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000)

How would I employ that methodoligy in the following 2 formulas so that it
would:
countif, based on 2 criterion...

=COUNTIF(--((J2:J2000=E8),--(K2:K2000=E9)),E8)


=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9))


and max(if, based on 2 criterion...

=MAX(IF(--((J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)))


Exactly that, but as an array formula, so commit with Ctrl-Shift-Enter, not
just Enter.

Multiple Criterion in a SUMIF function
 

The reason I ask about the other 2 formulas is because I am receiving "the
formula you typed contains an error" when I try to execute them. Any idea
why?
--
iperlovsky


"Bob Phillips" wrote:



"IPerlovsky" wrote in message
...
Thank you. I modified it slightly for simplicity as:

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000)

How would I employ that methodoligy in the following 2 formulas so that it
would:
countif, based on 2 criterion...

=COUNTIF(--((J2:J2000=E8),--(K2:K2000=E9)),E8)


=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9))


and max(if, based on 2 criterion...

=MAX(IF(--((J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)))


Exactly that, but as an array formula, so commit with Ctrl-Shift-Enter, not
just Enter.