Sumif function, nonadjacent cells
 

I need to pull totals from various places on the spreadsheet, and add them if
they are less than $0.00 to get the total amount owed. Is there any way to
do this? So far, nothing I've tried has worked.

For example:
Group 1 Total is cell K12
Group 2 Total is cell K44
Group 3 Total is cell K61
Group 4 Total is cell K86

The totals change frequently, sometimes they are positive numbers and
sometimes negative numbers. I want to have them all in a function that will
add only the negative ones. Right now, I have to change my function
everytime a total changes from positive to negative and vice versa.

Sumif function, nonadjacent cells
 

=SUMPRODUCT(SUMIF(INDIRECT({"K12","K44","K61","K86 "}),"<0"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"HFWS" wrote in message
...
I need to pull totals from various places on the spreadsheet, and add them
if
they are less than $0.00 to get the total amount owed. Is there any way
to
do this? So far, nothing I've tried has worked.

For example:
Group 1 Total is cell K12
Group 2 Total is cell K44
Group 3 Total is cell K61
Group 4 Total is cell K86

The totals change frequently, sometimes they are positive numbers and
sometimes negative numbers. I want to have them all in a function that
will
add only the negative ones. Right now, I have to change my function
everytime a total changes from positive to negative and vice versa.

Sumif function, nonadjacent cells
 

AFAIK, the SUMIF formula needs a continuous array.
So, why not create a workarea (possibly hidden) in unused columns, say
L1:L4.
Put in links: in L1: "=K12", L2: "=K44", L3: "=K61" L4: = "K86"

in Grand Total cell, enter: "=SUMIF(L1:L4","<0)

HTH
--
AP

"HFWS" a écrit dans le message de
...
I need to pull totals from various places on the spreadsheet, and add them
if
they are less than $0.00 to get the total amount owed. Is there any way
to
do this? So far, nothing I've tried has worked.

For example:
Group 1 Total is cell K12
Group 2 Total is cell K44
Group 3 Total is cell K61
Group 4 Total is cell K86

The totals change frequently, sometimes they are positive numbers and
sometimes negative numbers. I want to have them all in a function that
will
add only the negative ones. Right now, I have to change my function
everytime a total changes from positive to negative and vice versa.

Sumif function, nonadjacent cells
 

I'm not sure if I understand your problem well enough to give you a specific
answer, so I'll throw out a general answer instead. If it is not as simple
as testing those 4 cells, ie multiple rows are involved, you could use either
an array formula or sumproduct. A good explanation of how to use sumproduct
is at ...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Kevin Vaughn


"HFWS" wrote:

I need to pull totals from various places on the spreadsheet, and add them if
they are less than $0.00 to get the total amount owed. Is there any way to
do this? So far, nothing I've tried has worked.

For example:
Group 1 Total is cell K12
Group 2 Total is cell K44
Group 3 Total is cell K61
Group 4 Total is cell K86

The totals change frequently, sometimes they are positive numbers and
sometimes negative numbers. I want to have them all in a function that will
add only the negative ones. Right now, I have to change my function
everytime a total changes from positive to negative and vice versa.

Sumif function, nonadjacent cells
 

Some alternatives
=MIN(K12,0)+MIN(K44,0)+MIN(K61,0)+MIN(K86,0)
=K12*(K12<0)+K44*(K44<0)+K61*(K61<0)+K86*(K86<0)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"HFWS" wrote in message
...
I need to pull totals from various places on the spreadsheet, and add them
if
they are less than $0.00 to get the total amount owed. Is there any way
to
do this? So far, nothing I've tried has worked.

For example:
Group 1 Total is cell K12
Group 2 Total is cell K44
Group 3 Total is cell K61
Group 4 Total is cell K86

The totals change frequently, sometimes they are positive numbers and
sometimes negative numbers. I want to have them all in a function that
will
add only the negative ones. Right now, I have to change my function
everytime a total changes from positive to negative and vice versa.

Sumif function, nonadjacent cells
 

Hi
One way
=SUM(K12*(K12<0),K44*(K44<0),K61*(K61<0),K86*(K86< 0))

--
Regards

Roger Govier


"HFWS" wrote in message
...
I need to pull totals from various places on the spreadsheet, and add
them if
they are less than $0.00 to get the total amount owed. Is there any
way to
do this? So far, nothing I've tried has worked.

For example:
Group 1 Total is cell K12
Group 2 Total is cell K44
Group 3 Total is cell K61
Group 4 Total is cell K86

The totals change frequently, sometimes they are positive numbers and
sometimes negative numbers. I want to have them all in a function
that will
add only the negative ones. Right now, I have to change my function
everytime a total changes from positive to negative and vice versa.