| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on 2 criteria
I am trying to get a cell to sum based on 2 different ranges. Example, I have one column called status (which has a range of 0-5) and one called Type (which is based on SF, Acres, and Units). I've also got a 3rd column that is called size and it contains only numeric values that need to be summed up. I was trying to figure out how I could create a formula that is able to search all of the 1s in the first column and match it up with all of the "Acres" in the other column and give me a total summed up value based on a third numeric column. -- cubsfan ------------------------------------------------------------------------ cubsfan's Profile: http://www.excelforum.com/member.php...o&userid=33272 View this thread: http://www.excelforum.com/showthread...hreadid=534598 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing based on 2 criteria
There is a very powerful tool called SUMPRODUCT() that can help you. There
is also a really good explanation at: http://www.contextures.com/xlFunctio...tml#SumProduct -- Gary's Student "cubsfan" wrote: I am trying to get a cell to sum based on 2 different ranges. Example, I have one column called status (which has a range of 0-5) and one called Type (which is based on SF, Acres, and Units). I've also got a 3rd column that is called size and it contains only numeric values that need to be summed up. I was trying to figure out how I could create a formula that is able to search all of the 1s in the first column and match it up with all of the "Acres" in the other column and give me a total summed up value based on a third numeric column. -- cubsfan ------------------------------------------------------------------------ cubsfan's Profile: http://www.excelforum.com/member.php...o&userid=33272 View this thread: http://www.excelforum.com/showthread...hreadid=534598 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing based on 2 criteria
=SUMPRODUCT(--(A2:A200=1),--(B2:B200="Acres"),C2:C200)
Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "cubsfan" wrote in message ... I am trying to get a cell to sum based on 2 different ranges. Example, I have one column called status (which has a range of 0-5) and one called Type (which is based on SF, Acres, and Units). I've also got a 3rd column that is called size and it contains only numeric values that need to be summed up. I was trying to figure out how I could create a formula that is able to search all of the 1s in the first column and match it up with all of the "Acres" in the other column and give me a total summed up value based on a third numeric column. -- cubsfan ------------------------------------------------------------------------ cubsfan's Profile: http://www.excelforum.com/member.php...o&userid=33272 View this thread: http://www.excelforum.com/showthread...hreadid=534598 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return multiple rows of data based on criteria | Excel Worksheet Functions | |||
| Summing quantities based on like criteria? | Excel Discussion (Misc queries) | |||
| Calculating an average based on 2 and 3 criteria | Excel Worksheet Functions | |||
| Returning Results Based on Two Criteria | Excel Worksheet Functions | |||
| Counting & Summing based on criteria on another column | Excel Discussion (Misc queries) | |||
Linear Mode
