Sumif function, nonadjacent cells

[HFWS]

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.

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"HFWS" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Ardus Petus]

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" <[email protected]> a écrit dans le message de
news:[email protected]...
> 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.

[Kevin Vaughn]

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.

[Bernard Liengme]

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" <[email protected]> wrote in message
news:[email protected]...
>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.

[Roger Govier]

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

--
Regards

Roger Govier


"HFWS" <[email protected]> wrote in message
news:[email protected]...
>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.