Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF using 2 Criteria in 2 columns
I would be grateful if someone could offer their advice on how to solve a problem I am having with XL. I am trying to sum all the values in column C if column A and Column B meet the correct criteria. If Column A = X and Column B = B then Sum all the corresponding values in Column C. Column A Column B Column C X B 1 Y B 7 Z C 2 X B 3 Y C 10 (I.e. Answer = 4) My Excel book quotes the formula below: I.e. {SUM((A:A="X")*(B:B="B)*(C:C))} This formula does not look at the whole Column A only the cell in the same row as the formula. How can I get the formula to look at the entire array of data and return the sum of all the column C items that meet the correct conditions in Columns A &B? -- quailc ------------------------------------------------------------------------ quailc's Profile: http://www.excelforum.com/member.php...o&userid=34442 View this thread: http://www.excelforum.com/showthread...hreadid=542080 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF using 2 Criteria in 2 columns
=SUMPRODUCT(--(A1:A200="X"),--(B1:B200="B"),C1:C200)
Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "quailc" wrote in message ... I would be grateful if someone could offer their advice on how to solve a problem I am having with XL. I am trying to sum all the values in column C if column A and Column B meet the correct criteria. If Column A = X and Column B = B then Sum all the corresponding values in Column C. Column A Column B Column C X B 1 Y B 7 Z C 2 X B 3 Y C 10 (I.e. Answer = 4) My Excel book quotes the formula below: I.e. {SUM((A:A="X")*(B:B="B)*(C:C))} This formula does not look at the whole Column A only the cell in the same row as the formula. How can I get the formula to look at the entire array of data and return the sum of all the column C items that meet the correct conditions in Columns A &B? -- quailc ------------------------------------------------------------------------ quailc's Profile: http://www.excelforum.com/member.php...o&userid=34442 View this thread: http://www.excelforum.com/showthread...hreadid=542080 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF using 2 Criteria in 2 columns
Bob, Many Thanks for your advice with this. The SUMPRODUCT function works perfectly! You have saved me an awful lot of time messing around with this. Kind regards Corinne -- quailc ------------------------------------------------------------------------ quailc's Profile: http://www.excelforum.com/member.php...o&userid=34442 View this thread: http://www.excelforum.com/showthread...hreadid=542080 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF using 2 Criteria in 2 columns
I would be grateful if someone could offer their advice on how to solve a problem I am having with XL. Is it possible to perform the SUMPRODUCT function on a data range that is in a seperate workbook? I have tried selecting the range from another workbook but it comes back with #value result. How do I link the 2 workbooks together for this to work correctly? -- quailc ------------------------------------------------------------------------ quailc's Profile: http://www.excelforum.com/member.php...o&userid=34442 View this thread: http://www.excelforum.com/showthread...hreadid=542080 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF using 2 Criteria in 2 columns
Yes you can. Show us what you have tried.
-- HTH Bob Phillips (remove xxx from email address if mailing direct) "quailc" wrote in message ... I would be grateful if someone could offer their advice on how to solve a problem I am having with XL. Is it possible to perform the SUMPRODUCT function on a data range that is in a seperate workbook? I have tried selecting the range from another workbook but it comes back with #value result. How do I link the 2 workbooks together for this to work correctly? -- quailc ------------------------------------------------------------------------ quailc's Profile: http://www.excelforum.com/member.php...o&userid=34442 View this thread: http://www.excelforum.com/showthread...hreadid=542080 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF using 2 Criteria in 2 columns
I am trying to link my current workbook with: Workbook name: Leave Planner Apr 06 - Nov 06 working.xls Worksheet: May 06 =(SUMPRODUCT(1*('[Leave Planner Apr 06 - Nov 06 working.xls]May 06 '!$A$41:$A$68="A&C Cert"),1*('[Leave Planner Apr 06 - Nov 06 working.xls]May 06 '!$D$40="E")*'[Leave Planner Apr 06 - Nov 06 working.xls]May 06 '!$D$41:$D$68)) Thank you Corinne -- quailc ------------------------------------------------------------------------ quailc's Profile: http://www.excelforum.com/member.php...o&userid=34442 View this thread: http://www.excelforum.com/showthread...hreadid=542080 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf Criteria Matches | Excel Worksheet Functions | |||
Multiple Criteria in a SUMIF formula | Excel Discussion (Misc queries) | |||
SUMIF, multiple criteria | Excel Discussion (Misc queries) | |||
Sumif over multiple columns | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |