Sumif function, nonadjacent cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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({"K12","K44","K61","K86"}),"<0"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
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
 
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
 
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
 
Back
Top