SUMIF FORMULA

[Louie]

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!

 

[T. Valko]

Try this:

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

Biff

 

[Bob Phillips]

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)

 

[Lori]

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".