Sum range if 2 conditions are met

[Dean]

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

 

[Rick Rothstein]

Try this formula...

=SUMPRODUCT((O1:O53="AP")*(Q1:Q53="C")*(T1:T53))

 

[Dean]

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"

 

[Dave Peterson]

Give it a try.

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"

 

[Dean]

My bad
It worked like a charm!!!!! Thanks!!!!!

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]

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)