Hello, I have a sheet with totals both positive and negetive on it from say
c6 to t6, I need w6 to sum the totals that are between 5.01 and 50 both
positive and negetive, and give me the sum in a positive number so the
negetive don't cancel out the positives. ie the number 3 6 25 -3 and -14
appear, I need W6 to show 44 (6+25+14)
--
Thanks for your help, advice, ideas, and time.

Hi

You could use SUMIF:

=SUMIF(C6:T6,"=5.01")-SUMIF(C6:T6,"50")

or maybe SUMPRODUCT:

=SUMPRODUCT(--(C6:T6=5.01),--(C6:T6<=50),C6:T6)

Hope this helps!

Richard



KMH wrote:

Hello, I have a sheet with totals both positive and negetive on it from say
c6 to t6, I need w6 to sum the totals that are between 5.01 and 50 both
positive and negetive, and give me the sum in a positive number so the
negetive don't cancel out the positives. ie the number 3 6 25 -3 and -14
appear, I need W6 to show 44 (6+25+14)
--
Thanks for your help, advice, ideas, and time.

If the numbers are in A2:a6 try

=SUMPRODUCT(($A$2:$A$65)*
($A$2:$A$6<50),($A$2:$A$6))-
SUMPRODUCT(($A$2:$A$6<-5)*
($A$2:$A$6-50),($A$2:$A$6))

I could not get abs to work.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"KMH" wrote:

Hello, I have a sheet with totals both positive and negetive on it from say
c6 to t6, I need w6 to sum the totals that are between 5.01 and 50 both
positive and negetive, and give me the sum in a positive number so the
negetive don't cancel out the positives. ie the number 3 6 25 -3 and -14
appear, I need W6 to show 44 (6+25+14)
--
Thanks for your help, advice, ideas, and time.

=SUMPRODUCT(--(ABS(C6:T6)5),--(ABS(C6:T6<50)),ABS(C6:T6))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Martin Fishlock" wrote in message
...
If the numbers are in A2:a6 try

=SUMPRODUCT(($A$2:$A$65)*
($A$2:$A$6<50),($A$2:$A$6))-
SUMPRODUCT(($A$2:$A$6<-5)*
($A$2:$A$6-50),($A$2:$A$6))

I could not get abs to work.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"KMH" wrote:

Hello, I have a sheet with totals both positive and negetive on it from
say
c6 to t6, I need w6 to sum the totals that are between 5.01 and 50 both
positive and negetive, and give me the sum in a positive number so the
negetive don't cancel out the positives. ie the number 3 6 25 -3 and -14
appear, I need W6 to show 44 (6+25+14)
--
Thanks for your help, advice, ideas, and time.

Hi

=SUM(ABS(C6:G6)*(ABS(C6:G6)3)*(ABS(C6:G6<=50)))

entered as an array function (Ctrl+Shift+Enter)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"KMH" wrote in message
...
Hello, I have a sheet with totals both positive and negetive on it from
say
c6 to t6, I need w6 to sum the totals that are between 5.01 and 50 both
positive and negetive, and give me the sum in a positive number so the
negetive don't cancel out the positives. ie the number 3 6 25 -3 and -14
appear, I need W6 to show 44 (6+25+14)
--
Thanks for your help, advice, ideas, and time.