sum only negative numbers

[John]

How do I sum only the negative values associated with a
range of account codes in another column, i.e. I want the
total of accounts 510000 to 540000 that are in a negative
balance.

Thanks

 

[Bob Phillips]

John,

=SUM((A1:A100>=51000)*(A1:A100<=54000)*(B1:B100<0),(B1:B100))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[John]

Bob,

The SUMPRODUCT gives me a 0 value. Why would I want to
multiply anything?

 

[Peo Sjoblom]

If you get zero most likely your numbers are text
or you don't have any negative values where the account is between 51-54000.
Bob's formula works.The reason sumproduct is used is that each range
with criteria will return a set of Boolean TRUE and FALSE and multiplied
with a range of numbers it will sum the numbers that have a TRUE or 1 value.
First it might look like

=SUMPRODUCT(({FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE})*({TRUE
;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE})*({FALSE;FALSE;FALSE;FALS
E;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}),({0;0;2;4;6;-100;10;12;-100;16}))

then

when the 3 first conditions have been multiplied

=SUMPRODUCT({0;0;0;0;0;1;0;0;0;0},({0;0;2;4;6;-100;10;12;-100;16}))


now if the is a negative number in the last array where there is a 1 in the
firat array it will sum those
conditions so trhe above will return -100 and if it looked like

=SUMPRODUCT({0;0;0;0;0;1;0;0;1;0},({0;0;2;4;6;-100;10;12;-100;16}))

it would return -200

 

[Dave R.]

What it's multiplying is the evaluation (answer) of the expressions.
Expressions that are true = 1 and false = 0. In your earlier post you must
have specified some condition delated to 51000 and 54000 and less than 0,
and sum the range b1;b100 if those conditions exist.

e.g. if A1=51001 (TRUE = 1 for expression 1 and 2) and (*) B1= -1 (TRUE =
1), then sum B1 (and the rest of them that fit these conditions).

 

[John]

The explanation of the formula makes sense now, but the
result is still zero.

The data is sorted so it is all negative numbers, and the
accounts are numbers as well.

I can use sumif on each account, e.g. 510000 to get the
sum of numbers in acct 510000 etc. so that shows numbers
are there.

Can I send it to you to see?

 

[Peo Sjoblom]

You can send it to me, it definitely sounds as though your numbers are text,
that would explain
why sumif would work since it doesn't see any difference between 1 and "1"

(e-mail address removed)

or the email address at mvps.org that I use here, it has some very
aggressive
spam filtering though so the mindspring is a safer bet

 

[Peo Sjoblom]

John sent the workbook to me and the accounts were text.
I just did a copy and empty cell and paste special add and it worked fine..

 

[Harlan Grove]

John sent the workbook to me and the accounts were text.
I just did a copy and empty cell and paste special add and it worked fine..

...

Having had a few of these diagnostic back & forths, you didn't need to have the
OP send you the file. Just ask the OP to enter a formula like =ISNUMBER(X) where
X is the cell address of one of the 'numeric' accounts. If TRUE, there are still
other remote diagnostic approaches that could be used. If FALSE, the problem's
cause would have been isolated.

Don't encourage OPs to send files. Except for really pathological behavior,
they're not needed to diagnose formula errors.

 

[Peo Sjoblom]

... fine..
..

Having had a few of these diagnostic back & forths, you didn't need to have the
OP send you the file. Just ask the OP to enter a formula like =ISNUMBER(X) where
X is the cell address of one of the 'numeric' accounts. If TRUE, there are still
other remote diagnostic approaches that could be used. If FALSE, the problem's
cause would have been isolated.

Don't encourage OPs to send files. Except for really pathological behavior,
they're not needed to diagnose formula errors.

True.. Btw since we're discussing diagnostics I was doing an overhaul of the
time sheet template we are using at my work and I wanted to make it
adaptable so that you type in the year on page one and all the rest of the
sheet will adapt to that
and I used the workaround for the eomonth ATP function

=DATE(YEAR,MONTH+1,0)

to return the last date of the month and I was perplexed when I got a #NUM
error, eventually I checked the transition tab under tools>options and found
that it must originally have been made in Lotus since the [Transition
formula evaluation] was checked. Once I unchecked that it worked. Given your
knowledge of 123 why would that happen?

 

[Harlan Grove]

...
...

=DATE(YEAR,MONTH+1,0)

to return the last date of the month and I was perplexed when I got a #NUM
error, eventually I checked the transition tab under tools>options and found
that it must originally have been made in Lotus since the [Transition
formula evaluation] was checked. Once I unchecked that it worked. Given your
knowledge of 123 why would that happen?

Date functions are one place Excel has always been superior to 123 (credit where
due). 123's @DATE function is stupid. It doesn't accept months other than 1-12
nor days other that in the range appropriate for the given month (and year for
February). When fed anything invalid by its specs it returns ERR.