Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum range if 2 conditions are met
Hi,
I need to sum values for a range if two conditions are met. One column is a Status condition which is a simple Spin list ("AP"=Approved, "CP"=Cancelled and "TBD". The next column is a GL term "C"=Captial and "E"=Expence. For this example, lets just focus on Captital (Note-these are separate rows anyway - there will not be one row with both "C" and "E" but both are in the range). Columns O Q R T (status) (GL-"C") (GL-"E") (dollars) 10 AP C $ 10,000 11 AP E $ 5,000 12 CP C $ 20,000 13 AP C $ 20,000 14 TBD C $ 10,000 15 AP E $ 10,000 .. 53 Total approved Capital $ 30,000 (rows 10&13) If someone can help me on this one, I can figure out the rest...thanks DH |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum range if 2 conditions are met
Try this formula...
=SUMPRODUCT((O1:O53="AP")*(Q1:Q53="C")*(T1:T53)) -- Rick (MVP - Excel) "Dean" wrote in message ... Hi, I need to sum values for a range if two conditions are met. One column is a Status condition which is a simple Spin list ("AP"=Approved, "CP"=Cancelled and "TBD". The next column is a GL term "C"=Captial and "E"=Expence. For this example, lets just focus on Captital (Note-these are separate rows anyway - there will not be one row with both "C" and "E" but both are in the range). Columns O Q R T (status) (GL-"C") (GL-"E") (dollars) 10 AP C $ 10,000 11 AP E $ 5,000 12 CP C $ 20,000 13 AP C $ 20,000 14 TBD C $ 10,000 15 AP E $ 10,000 .. 53 Total approved Capital $ 30,000 (rows 10&13) If someone can help me on this one, I can figure out the rest...thanks DH |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum range if 2 conditions are met
Hi Thanks Rick...but I think it is multiplying. No what I needed.
Is the asterisk symbol multiplying in this formula? I just need it to sum the values in the range T10:T53, only of the status in column O="AP" and Column Q="C" "Rick Rothstein" wrote: Try this formula... =SUMPRODUCT((O10:O53="AP")*(Q10:Q53="C")*(T10:T53) ) -- Rick (MVP - Excel) "Dean" wrote in message ... Hi, I need to sum values for a range if two conditions are met. One column is a Status condition which is a simple Spin list ("AP"=Approved, "CP"=Cancelled and "TBD". The next column is a GL term "C"=Captial and "E"=Expence. For this example, lets just focus on Captital (Note-these are separate rows anyway - there will not be one row with both "C" and "E" but both are in the range). Columns O Q R T (status) (GL-"C") (GL-"E") (dollars) 10 AP C $ 10,000 11 AP E $ 5,000 12 CP C $ 20,000 13 AP C $ 20,000 14 TBD C $ 10,000 15 AP E $ 10,000 .. 53 Total approved Capital $ 30,000 (rows 10&13) If someone can help me on this one, I can figure out the rest...thanks DH |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum range if 2 conditions are met
Give it a try.
Dean wrote: Hi Thanks Rick...but I think it is multiplying. No what I needed. Is the asterisk symbol multiplying in this formula? I just need it to sum the values in the range T10:T53, only of the status in column O="AP" and Column Q="C" "Rick Rothstein" wrote: Try this formula... =SUMPRODUCT((O10:O53="AP")*(Q10:Q53="C")*(T10:T53) ) -- Rick (MVP - Excel) "Dean" wrote in message ... Hi, I need to sum values for a range if two conditions are met. One column is a Status condition which is a simple Spin list ("AP"=Approved, "CP"=Cancelled and "TBD". The next column is a GL term "C"=Captial and "E"=Expence. For this example, lets just focus on Captital (Note-these are separate rows anyway - there will not be one row with both "C" and "E" but both are in the range). Columns O Q R T (status) (GL-"C") (GL-"E") (dollars) 10 AP C $ 10,000 11 AP E $ 5,000 12 CP C $ 20,000 13 AP C $ 20,000 14 TBD C $ 10,000 15 AP E $ 10,000 .. 53 Total approved Capital $ 30,000 (rows 10&13) If someone can help me on this one, I can figure out the rest...thanks DH -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum range if 2 conditions are met
My bad
It worked like a charm!!!!! Thanks!!!!! "Dean" wrote: Hi Thanks Rick...but I think it is multiplying. No what I needed. Is the asterisk symbol multiplying in this formula? I just need it to sum the values in the range T10:T53, only of the status in column O="AP" and Column Q="C" "Rick Rothstein" wrote: Try this formula... =SUMPRODUCT((O10:O53="AP")*(Q10:Q53="C")*(T10:T53) ) -- Rick (MVP - Excel) "Dean" wrote in message ... Hi, I need to sum values for a range if two conditions are met. One column is a Status condition which is a simple Spin list ("AP"=Approved, "CP"=Cancelled and "TBD". The next column is a GL term "C"=Captial and "E"=Expence. For this example, lets just focus on Captital (Note-these are separate rows anyway - there will not be one row with both "C" and "E" but both are in the range). Columns O Q R T (status) (GL-"C") (GL-"E") (dollars) 10 AP C $ 10,000 11 AP E $ 5,000 12 CP C $ 20,000 13 AP C $ 20,000 14 TBD C $ 10,000 15 AP E $ 10,000 .. 53 Total approved Capital $ 30,000 (rows 10&13) If someone can help me on this one, I can figure out the rest...thanks DH |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum range if 2 conditions are met
Did you try the formula? If not, I think you might be surprised.
By the way, it looks like I added an additional set of parentheses (they won't affect the result, but they are not really needed). Here is the formula with them... =SUMPRODUCT((O10:O53="AP")*(Q10:Q53="C")*T10:T53) -- Rick (MVP - Excel) "Dean" wrote in message ... Hi Thanks Rick...but I think it is multiplying. No what I needed. Is the asterisk symbol multiplying in this formula? I just need it to sum the values in the range T10:T53, only of the status in column O="AP" and Column Q="C" "Rick Rothstein" wrote: Try this formula... =SUMPRODUCT((O10:O53="AP")*(Q10:Q53="C")*(T10:T53) ) -- Rick (MVP - Excel) "Dean" wrote in message ... Hi, I need to sum values for a range if two conditions are met. One column is a Status condition which is a simple Spin list ("AP"=Approved, "CP"=Cancelled and "TBD". The next column is a GL term "C"=Captial and "E"=Expence. For this example, lets just focus on Captital (Note-these are separate rows anyway - there will not be one row with both "C" and "E" but both are in the range). Columns O Q R T (status) (GL-"C") (GL-"E") (dollars) 10 AP C $ 10,000 11 AP E $ 5,000 12 CP C $ 20,000 13 AP C $ 20,000 14 TBD C $ 10,000 15 AP E $ 10,000 .. 53 Total approved Capital $ 30,000 (rows 10&13) If someone can help me on this one, I can figure out the rest...thanks DH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum a range if two conditions are met | Excel Discussion (Misc queries) | |||
Match 2 Conditions then Avg Range | Excel Discussion (Misc queries) | |||
Summing a range with conditions | Excel Worksheet Functions | |||
Add a Dynamic Range with 2 Conditions Q | Excel Worksheet Functions | |||
How do I sum a range after 2 different conditions are met (2 colu. | Excel Discussion (Misc queries) |