SUMIF FORMULA
 

Hello, I need to write a formula that sums totals from different sheets but,
I want it to ignore the negative numbers and add only the positive #'s

this is what I currently have

=SUM('week-1:Week-5'!K12)

it gives me a negative total because there are some negative numbers in some
cells
thank you for your help!
--
Louie

SUMIF FORMULA
 

Try this:

=SUMPRODUCT(SUMIF(INDIRECT("'week-"&{1,2,3,4,5}&"'!K12"),"0"))

Biff

"Louie" wrote in message
...
Hello, I need to write a formula that sums totals from different sheets
but,
I want it to ignore the negative numbers and add only the positive #'s

this is what I currently have

=SUM('week-1:Week-5'!K12)

it gives me a negative total because there are some negative numbers in
some
cells
thank you for your help!
--
Louie

SUMIF FORMULA
 

SUM does it as good, doesn't need SP

=SUM(SUMIF(INDIRECT("'week-"&{1,2,3,4,5}&"'!K12"),"0"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(SUMIF(INDIRECT("'week-"&{1,2,3,4,5}&"'!K12"),"0"))

Biff

"Louie" wrote in message
...
Hello, I need to write a formula that sums totals from different sheets
but,
I want it to ignore the negative numbers and add only the positive #'s

this is what I currently have

=SUM('week-1:Week-5'!K12)

it gives me a negative total because there are some negative numbers in
some
cells
thank you for your help!
--
Louie

SUMIF FORMULA
 

One way using only 3D-enabled formulas:

=AVEDEV(Rng,-SUM(Rng))*(COUNT(Rng)+1)/2+MIN(SUM(Rng),0)

where the name "Rng" refers to "=Week-1:Week-5'!K12".


On 18 Apr, 23:52, Louie wrote:
Hello, I need to write a formula that sums totals from different sheets but,
I want it to ignore the negative numbers and add only the positive #'s

this is what I currently have

=SUM('week-1:Week-5'!K12)

it gives me a negative total because there are some negative numbers in some
cells
thank you for your help!
--
Louie