need help with a simple xls equation !!

[Guest]

there is prob a very obvious answer but I need to use the "and" function with
the "sumif" function.

eg: 3 columns (a= account number b=product & c=value)
so:
sumif ( range=columns a:c ,criteria=(a="X" & b ="Y"), sum_range=c)

I need to use a single cell to calculate the equation?
Pls Hlp!

 

[Barb Reinhardt]

This should help

http://www.j-walk.com/ss/excel/tips/tip74.htm

 

[swatsp0p]

You need to use SUMPRODUCT to do this, as such:

=SUMPRODUCT(--(A1:A100=nn),--(B1:B100="abc"), C1:C100)

where your account number is nn and your product is "abc" and the range
of the data table is A1:C100

note that the values for 'nn' and "abc" can also be a cell reference
that holds the desired values, as such:

=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1), C1:C100)

where D1 contains the account number and E1 holds the product.

Good Luck

Bruce

 

[malik641]

there is prob a very obvious answer but I need to use the "and" function
with
the "sumif" function.

eg: 3 columns (a= account number b=product & c=value)
so:
sumif ( range=columns a:c ,criteria=(a="X" & b ="Y"), sum_range=c)

I need to use a single cell to calculate the equation?
Pls Hlp!

This should work....try

{=SUM((A1:A60000="X")*(B1:B60000="Y")*(C1:C60000))}

I couldn't use A:A and B:B and C:C for the ranges because I would get a
#NUM! error (and I think it's because the value is too high....but I'm
not sure). This works just fine, though. Unless you plan to use more
than 60,000 rows of info.

Don't forget that this is an array formula and the brackets should not
be entered manually. Leave the brackets out and when you're finished
writing the equation press 'CTRL'+'SHIFT'+'ENTER' and the brackets will
be placed and it will become an array formula.

Hope this helps