AND statements in a SUMIF (or a lookup)

[Guest]

I am trying to create a SUMIF (or a LOOKUP) which evaluates more than one
criteria by including an AND statement, but cannot get this to work.

I have tried the below formulas without success.

=SUMIF(A1:M500,AND(A20,L2),I:I)

=VLOOKUP(AND(A20,L2),A1:M500,5,FALSE)

Any suggestions? Is this possible?

 

[JulieD]

Hi Matt

what are you trying to do as the formulas at the moment don't make much
sense to me
with a SUMIF the parameters are
=SUMIF(range to check for the criteria, criteria, range to sum)
as far as i know the range to check for the criteria needs to be a single
column not a matrix and the criteria needs to be able to be evaluated (e.g.
A20=5 or L2="cat")

with VLOOKUP you can only lookup one thing, because it must be in the left
most column of the table - you can concatenate two cells into a lookup value
e.g.
=VLOOKUP(A20 & " " & L2,B1:M500,5,0)
if column B had something like (using my 1st example "5 cat" as an entry)

Additionally, your criteria range is in your lookup range...which won't
work. So if you'ld like to type out a sample of your data and explain what
you're trying to achieve (please don't attach a workbook) - we might be able
to advise a suitable solution.

Cheers
JulieD

 

[Jason Morin]

SUMIF cannot handle multiple criteria. Use SUMPRODUCT.
There are a lot of great examples of using SUMPRODUCT to
sum and count based on multiple criteria.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
Jason
Atlanta, GA