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

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

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

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

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

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