Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help: Summing Multiple Criteria
I need to sum different criterias. I have three columns. A, B, and C. Sample 1234 1235 20 1235 1231 15 1236 1236 10 I need the formula to match the data in column B to column A. If it match, the formula must total the amount in column C. I hope someone can help me with this. Thanks in advance! -- japorms ------------------------------------------------------------------------ japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544 View this thread: http://www.excelforum.com/showthread...hreadid=537219 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help: Summing Multiple Criteria
If I am reading the request correctly you would only have one result where
1236 = 1236. In that case, you would need an IF formula. =IF(B4=A4,C4,0). This states that if the amount in column B equals column A take the column C value. If it does not use 0 value. You can change 0 value to anything you would like, if you would like words replace 0 with "words" "japorms" wrote: I need to sum different criterias. I have three columns. A, B, and C. Sample 1234 1235 20 1235 1231 15 1236 1236 10 I need the formula to match the data in column B to column A. If it match, the formula must total the amount in column C. I hope someone can help me with this. Thanks in advance! -- japorms ------------------------------------------------------------------------ japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544 View this thread: http://www.excelforum.com/showthread...hreadid=537219 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help: Summing Multiple Criteria
If you want a single cell to contain the sum from column C of all
corresponding matches between A and B in rows 1 to 100 (for example), you can use this formula: =SUM(IF((A1:A100=B1:B100),C1:C100,0)) It is an array formula, which means that once you have typed it in (or subsequently edit it) you have to use CTRL-SHIFT-ENTER instead of just ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula - you must not type these yourself. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help: Summing Multiple Criteria
Nope, its not exactly like that I'll rephrase my question. I think I'm a bit off in my first post. There will be 2 sheet now to make it much clearer. Sheet1 and Sheet2 In sheet1, the data are the Serial No. and Amount like so: 1235 10 1236 20 1230 30 In sheet2, the data are serial no only, like so: 1236 1232 1230 I need to sum all the amount that will match in sheet2 like for my example sheet will match with 1230 and 1236, so the results should be 50. I've been trying a lot of formulas, and it's confusing me. I hope you can help me with this one. This one is a challenge. Thanks! T Duquette Wrote: If I am reading the request correctly you would only have one result where 1236 = 1236. In that case, you would need an IF formula. =IF(B4=A4,C4,0). This states that if the amount in column B equals column A take the column C value. If it does not use 0 value. You can change 0 value to anything you would like, if you would like words replace 0 with "words" "japorms" wrote: I need to sum different criterias. I have three columns. A, B, and C. Sample 1234 1235 20 1235 1231 15 1236 1236 10 I need the formula to match the data in column B to column A. If it match, the formula must total the amount in column C. I hope someone can help me with this. Thanks in advance! -- japorms ------------------------------------------------------------------------ japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544 View this thread: http://www.excelforum.com/showthread...hreadid=537219 -- japorms ------------------------------------------------------------------------ japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544 View this thread: http://www.excelforum.com/showthread...hreadid=537219 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help: Summing Multiple Criteria
It's still not working, the results should be 50 -- japorms ------------------------------------------------------------------------ japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544 View this thread: http://www.excelforum.com/showthread...hreadid=537219 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help: Summing Multiple Criteria
=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!A1:A10,Sheet2!A1:A3,0))),Sh eet1!B1:B10)
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "japorms" wrote in message ... Nope, its not exactly like that I'll rephrase my question. I think I'm a bit off in my first post. There will be 2 sheet now to make it much clearer. Sheet1 and Sheet2 In sheet1, the data are the Serial No. and Amount like so: 1235 10 1236 20 1230 30 In sheet2, the data are serial no only, like so: 1236 1232 1230 I need to sum all the amount that will match in sheet2 like for my example sheet will match with 1230 and 1236, so the results should be 50. I've been trying a lot of formulas, and it's confusing me. I hope you can help me with this one. This one is a challenge. Thanks! T Duquette Wrote: If I am reading the request correctly you would only have one result where 1236 = 1236. In that case, you would need an IF formula. =IF(B4=A4,C4,0). This states that if the amount in column B equals column A take the column C value. If it does not use 0 value. You can change 0 value to anything you would like, if you would like words replace 0 with "words" "japorms" wrote: I need to sum different criterias. I have three columns. A, B, and C. Sample 1234 1235 20 1235 1231 15 1236 1236 10 I need the formula to match the data in column B to column A. If it match, the formula must total the amount in column C. I hope someone can help me with this. Thanks in advance! -- japorms ------------------------------------------------------------------------ japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544 View this thread: http://www.excelforum.com/showthread...hreadid=537219 -- japorms ------------------------------------------------------------------------ japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544 View this thread: http://www.excelforum.com/showthread...hreadid=537219 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help: Summing Multiple Criteria
Thanks Bob! It's working great now. Bob Phillips Wrote: =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!A1:A10,Sheet2!A1:A3,0))),Sh eet1!B1:B10) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "japorms" wrote in message ... Nope, its not exactly like that I'll rephrase my question. I think I'm a bit off in my first post. There will be 2 sheet now to make it much clearer. Sheet1 and Sheet2 In sheet1, the data are the Serial No. and Amount like so: 1235 10 1236 20 1230 30 In sheet2, the data are serial no only, like so: 1236 1232 1230 I need to sum all the amount that will match in sheet2 like for my example sheet will match with 1230 and 1236, so the results should be 50. I've been trying a lot of formulas, and it's confusing me. I hope you can help me with this one. This one is a challenge. Thanks! T Duquette Wrote: If I am reading the request correctly you would only have one result where 1236 = 1236. In that case, you would need an IF formula. =IF(B4=A4,C4,0). This states that if the amount in column B equals column A take the column C value. If it does not use 0 value. You can change 0 value to anything you would like, if you would like words replace 0 with "words" "japorms" wrote: I need to sum different criterias. I have three columns. A, B, and C. Sample 1234 1235 20 1235 1231 15 1236 1236 10 I need the formula to match the data in column B to column A. If it match, the formula must total the amount in column C. I hope someone can help me with this. Thanks in advance! -- japorms ------------------------------------------------------------------------ japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544 View this thread: http://www.excelforum.com/showthread...hreadid=537219 -- japorms ------------------------------------------------------------------------ japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544 View this thread: http://www.excelforum.com/showthread...hreadid=537219 -- japorms ------------------------------------------------------------------------ japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544 View this thread: http://www.excelforum.com/showthread...hreadid=537219 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing Fields with Multiple Criteria | Excel Discussion (Misc queries) | |||
Counting Using Multiple Criteria | Excel Worksheet Functions | |||
Retrieving a Value from List that meets multiple Criteria | Excel Discussion (Misc queries) | |||
Multiple criteria LOOKUP | Excel Worksheet Functions | |||
Multiple Criteria IF Nesting | Excel Worksheet Functions |