Home |
Search |
Today's Posts |
#1
|
|||
|
|||
need help with a simple xls equation !!
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! |
#2
|
|||
|
|||
This should help
http://www.j-walk.com/ss/excel/tips/tip74.htm "CC-Khriz" wrote in message ... 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! |
#3
|
|||
|
|||
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 -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=381960 |
#4
|
|||
|
|||
CC-Khriz Wrote: 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 -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=381960 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is mathematical equation for NORMDIST (x,mean,standard_dev,TR | Excel Worksheet Functions | |||
Help with what should be a simple formula | Excel Worksheet Functions | |||
convert equation to formula | Excel Discussion (Misc queries) | |||
Rounding in Trendline Equation | Charts and Charting in Excel | |||
Logarithmic Trendline Equation | Charts and Charting in Excel |