SUM positive and negative nos 2

[mart10]

I am using Excel 2003
I have a list of numbers as below example. The list may vary in length and sign of numbers may change on any row

-2
-4
6
-15
9

I want to sum the neagive and positive number in the column seperately. So i would expect to see answers of -21 and 15

 

[GlennUK]

That'd be:
=SUMIF(A1:A5,"<0")
and
=SUMIF(A1:A5,">0")

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Andrzejek]

Sort the column, use SUM function at the end of negative numbers, do the same for positive numbers.
[tt]
A B
-15
-4
-2 -21
6
9 15
[/tt]

Have fun.

---- Andy

 

[mart10]

Hi cant sort the column, The idea works, but wnat to now expand the idea I have data structured as following example

-1 A
4 F
-5 F
0 F
-77 F
-22 F
9 A

this structure repeats for a number of days so what I now need to do is to sum as before but with extra condition that column B value must equal F

 

[jbra]

you need sumifs

=SUMIFS(A1:A7,A1:A7,">0",B1:B7,"F")

 

[GlennUK]

So that'd be:

=SUMPRODUCT(--(B1:B7="F")*--(A1:A7>0)*A1:A7)

and

=SUMPRODUCT(--(B1:B7="F")*--(A1:A7<0)*A1:A7)

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[mart10]

Are you sure formula correct. based on my columns I put in

=SUMIFS('Balance & Cash Flow Forecasts'!F6:F20,">0",'Balance & Cash Flow Forecasts'!Z6:Z20,"F")

and it didnt work

 

[GlennUK]

SUMIFS is not available in Excel 2003 ... use my solution.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[N1GHTEYES]

Or an array formula:

={SUM(If(B1:B7="F",If(A1:A7>0,A1:A7)))}

Glen: I've encountered people using the

SUMPRODUCT(--

construct before on TT, and a while back I figured out what on earth it means, but I've forgotten. Could you remind me please? The help seems quite silent on the topic.

Tony

 

[N1GHTEYES]

Just to clarify: I understand SUMPRODUCT (at least I think I do!). It is the -- operator I'm puzzled about.

Tony

 

[SkipVought]

-([numeric value])

is [numeric value] * -1

--([numeric value])

is [numeric value] * -1 * -1



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

Skip - sorry, it seems like you are saying that:

--(A1:A7) is equivalent to (A1:A7)

i.e. -1*-1 = +1

In which case why use the -- construct in the first place? How does this relate to its use in the expression:

=SUMPRODUCT(--(B1:B7="F")*--(A1:A7>0)*A1:A7)?

Tony

 

[SkipVought]

Because when you have an EQUALITY, in an range reference, each equality in the range, evaluates to TRUE or FALSE and the double unary coerces a calculation for each equality.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

This page talks about using a few diff methods, including sumproduct and array formulae:

http://www.ozgrid.com/Excel/sum-if.htm

In case you're interested.

I'd probably use an Array just b/c that's what I tend to think of with multiple criteria.

 

[N1GHTEYES]

kjv1611 Thanks for the reference.

Skip: I get you. Thanks for explaining.

But doesn't the * force an evaluation anyway?

In other words, instead of

=SUMPRODUCT(--(B1:B7="F")*--(A1:A7>0)*A1:A7)

wouldn't

=SUMPRODUCT((B1:B7="F")*(A1:A7>0)*A1:A7)
or even
=SUMPRODUCT((B1:B7="F")*(A1:A7>0),A1:A7)

work just as well? (actually they do - I've just tried it)

And while we're on the topic, the Help syntax for SUMPRODUCT separates the entities using "," (as in the second example above) not "*". How come people use "*", and how come it works?

Tony

 

[SkipVought]

In THIS case the coersion accurs by virtue of the numeric value that are in the PRODUCT...
[tt]
A B
1
2 F
0 F
4
5 F
6
7

=SUMPRODUCT((B1:B7="F")*(A1:A7>0)*A1:A7)

=((FALSE) * (TRUE) * (1))
=((TRUE) * (TRUE) * (2))
=((TRUE) * (FALSE) * (0))
=((FALSE) * (TRUE) * (4))
=((TRUE) * (TRUE) * (5))
=((FALSE) * (TRUE) * (6))
=((FALSE) * (TRUE) * (7))
[/tt]
But this one have only TRUE or FALSE to sum...
[tt]
A B
1
2 F
0 F
4
5 F
6
7

=SUMPRODUCT((B1:B7="F")*(A1:A7>0))

=((FALSE) * (TRUE))
=((TRUE) * (TRUE))
=((TRUE) * (FALSE))
=((FALSE) * (TRUE))
=((TRUE) * (TRUE))
=((FALSE) * (TRUE))
=((FALSE) * (TRUE))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

Another way uses the inbuilt ABS function:
=SUMPRODUCT(ABS(myRange))


Gavin

 

[N1GHTEYES]

Skip - Thanks for taking the time to help me get my brain around this. I understand what you wrote, but I don't understand the relevance. In both cases the formulae you wrote work, and in neither case do they use the -- construct. So, my questions remain:

1 If you are including a multiplication inside the formula, which in itself appears to coerce the booleans to be evaluated numerically, why use the --?

2 Given that the help says the syntax for SUMPRODUCT is:
=SUMPRODUCT(range1,range2), why does:
=SUMPRODUCT(range1*range2) also work? And is there any advantage to using "*" in preference to ",".

Tony

 

[SkipVought]

I do know that I rarely use the double unary, but there have been a very few instances in the murky past, that it seemed to be necessary.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlennUK]

I do know that I rarely use the double unary, but there have been a very few instances in the murky past, that it seemed to be necessary.

... and it's because of those instances that I use the double-unary. After you've spent a few hours trying to debug one of these, and finding that the double-unary fixes it, you tend to go that way every time.

Cheers, Glenn.

Beauty is in the eye of the beerholder.