need help with a simple xls equation !!

[CC-Khriz]

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!

[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

[Barb Reinhardt]

This should help

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

"CC-Khriz" <[email protected]> wrote in message
news:[email protected]...
> 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!