Creating a formula for two columns with Criteria
 

A B
1 10 20
2 10 0
3 10 -5
4 10 15
5 10 0

Alright say you have this as your data field. I know that using the SUMIF
function will give me the total of A if B 0 (or whatever I set the criteria
to), BUT how can I get the sum of A AND B if B is greater than 0

Creating a formula for two columns with Criteria
 

I'd use something like:
=SUMIF(B:B,""&0,A:A)+SUMIF(B:B,""&0)
or
=SUMIF(B:B,""&0,A:A)+SUMIF(B:B,""&0,B:B)



smorrison wrote:

A B
1 10 20
2 10 0
3 10 -5
4 10 15
5 10 0

Alright say you have this as your data field. I know that using the SUMIF
function will give me the total of A if B 0 (or whatever I set the criteria
to), BUT how can I get the sum of A AND B if B is greater than 0

--

Dave Peterson

Creating a formula for two columns with Criteria
 

Try this:

=SUMPRODUCT((B1:B50)*((B1:B5)+(A1:A5)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"smorrison" wrote in message
...
A B
1 10 20
2 10 0
3 10 -5
4 10 15
5 10 0

Alright say you have this as your data field. I know that using the SUMIF
function will give me the total of A if B 0 (or whatever I set the
criteria
to), BUT how can I get the sum of A AND B if B is greater than 0

Creating a formula for two columns with Criteria
 

Slightly shorter, and more elegant

=SUMPRODUCT((B1:B50)*A1:B5)


"Ragdyer" wrote:

Try this:

=SUMPRODUCT((B1:B50)*((B1:B5)+(A1:A5)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"smorrison" wrote in message
...
A B
1 10 20
2 10 0
3 10 -5
4 10 15
5 10 0

Alright say you have this as your data field. I know that using the SUMIF
function will give me the total of A if B 0 (or whatever I set the
criteria
to), BUT how can I get the sum of A AND B if B is greater than 0

Creating a formula for two columns with Criteria
 

Yes ... more elegant!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Teethless mama" wrote in message
...
Slightly shorter, and more elegant

=SUMPRODUCT((B1:B50)*A1:B5)


"Ragdyer" wrote:

Try this:

=SUMPRODUCT((B1:B50)*((B1:B5)+(A1:A5)))

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"smorrison" wrote in message
...
A B
1 10 20
2 10 0
3 10 -5
4 10 15
5 10 0

Alright say you have this as your data field. I know that using the
SUMIF
function will give me the total of A if B 0 (or whatever I set the
criteria
to), BUT how can I get the sum of A AND B if B is greater than 0