Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas
I have a work sheet that contains a variety of columns. Three columns are of
interest. The first, say A, contains years (2006, 2005, 2004 etc.). The second column, B, contains Names (Jim, Dick, Bob etc.). The third column, C, contains a numbers. What I need to do, but I'm having problems figuring it out is, I want to add the values of column C that meet the criteria of each column A and B. For example I want to add all the values of C that are 2006 (column A) AND Jim (column B). I've been playing around with formulas but I haven't managed to figure it out with trying to match the two columns. If anyone could help or has any ideas I would appreciate any direction I could get. Thank you in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas
=SUMPRODUCT(--($A$3:$A$15=$A1),--($B$3:$B$15=B$1),$C$3:$C$15)
enter 2006 in cell a1 and Jim in cell b1 note the area's need to be sized the same i.e. 3 to 15 for this example. -- Greetings from New Zealand Bill K "R" wrote in message ... I have a work sheet that contains a variety of columns. Three columns are of interest. The first, say A, contains years (2006, 2005, 2004 etc.). The second column, B, contains Names (Jim, Dick, Bob etc.). The third column, C, contains a numbers. What I need to do, but I'm having problems figuring it out is, I want to add the values of column C that meet the criteria of each column A and B. For example I want to add all the values of C that are 2006 (column A) AND Jim (column B). I've been playing around with formulas but I haven't managed to figure it out with trying to match the two columns. If anyone could help or has any ideas I would appreciate any direction I could get. Thank you in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas
Use the sumproduct function. This acts like the SUMIF function, but uses two
qualifiers. =SUMPRODUCT(--(A1:A3=2005),--(B1:B3="Jim"),C1:C3) "R" wrote: I have a work sheet that contains a variety of columns. Three columns are of interest. The first, say A, contains years (2006, 2005, 2004 etc.). The second column, B, contains Names (Jim, Dick, Bob etc.). The third column, C, contains a numbers. What I need to do, but I'm having problems figuring it out is, I want to add the values of column C that meet the criteria of each column A and B. For example I want to add all the values of C that are 2006 (column A) AND Jim (column B). I've been playing around with formulas but I haven't managed to figure it out with trying to match the two columns. If anyone could help or has any ideas I would appreciate any direction I could get. Thank you in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas
You need this: =SUMPRODUCT(--(A1:A100=2006),--(B1:B100="Jim")*C1:C100) Replace the 2006 with a cell containing that search value and do the same with "Jim" -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=498447 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif formulas change after doing a sort | Excel Worksheet Functions | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Problem with named formula's | Excel Worksheet Functions | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |