software

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.

=SUMPRODUCT(SUMIF(INDIRECT({quot;K12quot;,quot;K44quot;,quot;K61quot;,quot;K86 quot;}),quot;lt;0quot;))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;HFWSquot; gt; wrote in message
...
gt; I need to pull totals from various places on the spreadsheet, and add them
if
gt; they are less than $0.00 to get the total amount owed. Is there any way
to
gt; do this? So far, nothing I've tried has worked.
gt;
gt; For example:
gt; Group 1 Total is cell K12
gt; Group 2 Total is cell K44
gt; Group 3 Total is cell K61
gt; Group 4 Total is cell K86
gt;
gt; The totals change frequently, sometimes they are positive numbers and
gt; sometimes negative numbers. I want to have them all in a function that
will
gt; add only the negative ones. Right now, I have to change my function
gt; everytime a total changes from positive to negative and vice versa.
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: quot;=K12quot;, L2: quot;=K44quot;, L3: quot;=K61quot; L4: = quot;K86quot;

in Grand Total cell, enter: quot;=SUMIF(L1:L4quot;,quot;lt;0)

HTH
--
AP

quot;HFWSquot; gt; a écrit dans le message de
...
gt; I need to pull totals from various places on the spreadsheet, and add them
if
gt; they are less than $0.00 to get the total amount owed. Is there any way
to
gt; do this? So far, nothing I've tried has worked.
gt;
gt; For example:
gt; Group 1 Total is cell K12
gt; Group 2 Total is cell K44
gt; Group 3 Total is cell K61
gt; Group 4 Total is cell K86
gt;
gt; The totals change frequently, sometimes they are positive numbers and
gt; sometimes negative numbers. I want to have them all in a function that
will
gt; add only the negative ones. Right now, I have to change my function
gt; everytime a total changes from positive to negative and vice versa.
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 ...

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

--
Kevin Vaughnquot;HFWSquot; wrote:

gt; I need to pull totals from various places on the spreadsheet, and add them if
gt; they are less than $0.00 to get the total amount owed. Is there any way to
gt; do this? So far, nothing I've tried has worked.
gt;
gt; For example:
gt; Group 1 Total is cell K12
gt; Group 2 Total is cell K44
gt; Group 3 Total is cell K61
gt; Group 4 Total is cell K86
gt;
gt; The totals change frequently, sometimes they are positive numbers and
gt; sometimes negative numbers. I want to have them all in a function that will
gt; add only the negative ones. Right now, I have to change my function
gt; everytime a total changes from positive to negative and vice versa.

Some alternatives
=MIN(K12,0) MIN(K44,0) MIN(K61,0) MIN(K86,0)
=K12*(K12lt;0) K44*(K44lt;0) K61*(K61lt;0) K86*(K86lt;0)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from emailquot;HFWSquot; gt; wrote in message
...
gt;I need to pull totals from various places on the spreadsheet, and add them
gt;if
gt; they are less than $0.00 to get the total amount owed. Is there any way
gt; to
gt; do this? So far, nothing I've tried has worked.
gt;
gt; For example:
gt; Group 1 Total is cell K12
gt; Group 2 Total is cell K44
gt; Group 3 Total is cell K61
gt; Group 4 Total is cell K86
gt;
gt; The totals change frequently, sometimes they are positive numbers and
gt; sometimes negative numbers. I want to have them all in a function that
gt; will
gt; add only the negative ones. Right now, I have to change my function
gt; everytime a total changes from positive to negative and vice versa.
Hi
One way
=SUM(K12*(K12lt;0),K44*(K44lt;0),K61*(K61lt;0),K86*(K86lt; 0))

--
Regards

Roger Govierquot;HFWSquot; gt; wrote in message
...
gt;I need to pull totals from various places on the spreadsheet, and add
gt;them if
gt; they are less than $0.00 to get the total amount owed. Is there any
gt; way to
gt; do this? So far, nothing I've tried has worked.
gt;
gt; For example:
gt; Group 1 Total is cell K12
gt; Group 2 Total is cell K44
gt; Group 3 Total is cell K61
gt; Group 4 Total is cell K86
gt;
gt; The totals change frequently, sometimes they are positive numbers and
gt; sometimes negative numbers. I want to have them all in a function
gt; that will
gt; add only the negative ones. Right now, I have to change my function
gt; everytime a total changes from positive to negative and vice versa.