need help with a simple xls equation !!

C

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!
 
S

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
 
M

malik641

CC-Khriz said:
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!
Click to expand...
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
 
Top