Sum if negative

Hi,

Attached are 5 numbers from d5-h5

in the attrition cell I need to sum the negative cells
In the growth cell I need to sum the positive cells

What formula would I use?

Thanks
EE.xlsx
Seamus2626Asked:
Who is Participating?
Advertise Here
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
So why can't you use SUMIF?

If you can't, then:

=MIN(D4,0)+MIN(E4,0)+MIN(F4,0)+MIN(G4,0)+MIN(H4,0)
=MAX(D4,0)+MAX(E4,0)+MAX(F4,0)+MAX(G4,0)+MAX(H4,0)

and if I haven't got it right, please tell me exactly what results you are looking for.
0
 
Rob HensonFinance AnalystCommented:
SImple SUMIF will do this for you:

=SUMIF(D5:H5,"<"&0,D5:H5)

=SUMIF(D5:H5,">"&0,D5:H5)

Thanks
Rob
0
 
SimonCommented:
Use
Attrition:
=SUMIF(D4:H4,"<0")
Growth:
=SUMIF(D4:H4,">0")
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Now
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
=SUMIF(D4:H4,"<0")

And

=SUMIF(D4:H4,">0")
0
 
Seamus2626Author Commented:
Hi guys, I cant use sumif

For the example attached - attrition must = -289 and growth = 940

Thanks
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
How do you calculate that? And why can't you use SUMIF?
0
 
Rob HensonFinance AnalystCommented:
So:

Attrition = 651-940=-289

Growth = MAX(D5:H5) = 940
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Then attrition is =h4-max(d4:h4) then?
0
 
Rob HensonFinance AnalystCommented:
I am assuming that the headers for each column are geographical regions:

ASP - Asia Pacific
EUR - Europe
LATAM - Latin America
ME - Middle East
NA - North America

Are you comparing like with like?

If correct to do so, I assume the Highest and next highest for the 651-940 calculation could be in any of the 5 cells.
0
 
Seamus2626Author Commented:
Hi guys, sorry for not being clearer here.

It is for regions

Some may be growing, some may be declining

I need the sum of the numbers that are growing
and

the sum of the numbers that are declining

So the message is - from your 5 regions, and your net growth of 651 - you are growing 940 and declining 289

Hope that makes sense!
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No, it doesn't.

You have figures of -20      -65      940      0      651

The total of the +ve figures is 940+651=1591
The total of the -ve figures is -20-65 = -85
0
 
Seamus2626Author Commented:
I see I got my numbers mixed up when doing up a new workbook

Im looking at

ASP= -204
EUR= -20
LATAM= -65
ME = 940
NA= 0

Thanks
0
 
Seamus2626Author Commented:
Perfect!

Thanks Phillip
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.