Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

SImple SUMIF will do this for you:

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

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

Thanks
Rob
Use
Attrition:
=SUMIF(D4:H4,"<0")
Growth:
=SUMIF(D4:H4,">0")
Avatar of Phillip Burton
Phillip Burton

=SUMIF(D4:H4,"<0")

And

=SUMIF(D4:H4,">0")
Avatar of Seamus2626

ASKER

Hi guys, I cant use sumif

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

Thanks
How do you calculate that? And why can't you use SUMIF?
So:

Attrition = 651-940=-289

Growth = MAX(D5:H5) = 940
Then attrition is =h4-max(d4:h4) then?
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.
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!
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
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
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect!

Thanks Phillip