I have the following SUMIF statement but i would like to modify the formula
so it only counts the the negative numbers, i tried if<0 but no luck.

=SUMIF(B2:B122,C123,D2:D122)

the D reference should say somewhere 'if less than 0' - so it only counts
negative numbers.

thanks for your help.

One way:
=SUMPRODUCT((B2:B122=C123)*(D2:D1220)*D2:D122)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Zak" wrote:
I have the following SUMIF statement but i would like to modify the formula
so it only counts the the negative numbers, i tried if<0 but no luck.

=SUMIF(B2:B122,C123,D2:D122)

the D reference should say somewhere 'if less than 0' - so it only counts
negative numbers.

thanks for your help.

THANKS, that worked great but just to understand it for future purposes..
what are the two * doing and why do you make refernce to the D bit twice?

Do you have syntax for this? for eg, sumif syntax is range, criteria and sum
range - what would it be for this? just trying to make sense of it.

thanks alot

"Max" wrote:

One way:
=SUMPRODUCT((B2:B122=C123)*(D2:D1220)*D2:D122)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Zak" wrote:
I have the following SUMIF statement but i would like to modify the formula
so it only counts the the negative numbers, i tried if<0 but no luck.

=SUMIF(B2:B122,C123,D2:D122)

the D reference should say somewhere 'if less than 0' - so it only counts
negative numbers.

thanks for your help.

Hi,

i think there is a problem with the formula, it is working but not picking
up the correct value.

i have emailed you on your yahoo address to ask if its ok that i email you
the sample spreadsheet so you can see the problem.

thanks alot.

"Max" wrote:

One way:
=SUMPRODUCT((B2:B122=C123)*(D2:D1220)*D2:D122)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Zak" wrote:
I have the following SUMIF statement but i would like to modify the formula
so it only counts the the negative numbers, i tried if<0 but no luck.

=SUMIF(B2:B122,C123,D2:D122)

the D reference should say somewhere 'if less than 0' - so it only counts
negative numbers.

thanks for your help.

Pl do not email

Post a link to your sample* file here.

This way, the ensuing discussions
stays visible to all who may be interested.

You could use this to upload:
http://www.freefilehosting.net/

*desensitized as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On Fri, 1 Feb 2008 03:38:15 -0800, Zak wrote:

I have the following SUMIF statement but i would like to modify the formula
so it only counts the the negative numbers, i tried if<0 but no luck.

=SUMIF(B2:B122,C123,D2:D122)

the D reference should say somewhere 'if less than 0' - so it only counts
negative numbers.

thanks for your help.


To count negative numbers:

=countif(rng,"<0")

Perhaps you want to do something else?



--ron

But i need to have the other references in there too..

=SUMIF(B2:B122,C123,D2:D122)

where would i insert your 'countif' bit in?

If this works then i need not post my sample file but if it doesnt i will.

thanks.

"Ron Rosenfeld" wrote:

On Fri, 1 Feb 2008 03:38:15 -0800, Zak wrote:

I have the following SUMIF statement but i would like to modify the formula
so it only counts the the negative numbers, i tried if<0 but no luck.

=SUMIF(B2:B122,C123,D2:D122)

the D reference should say somewhere 'if less than 0' - so it only counts
negative numbers.

thanks for your help.


To count negative numbers:

=countif(rng,"<0")

Perhaps you want to do something else?



--ron

Hi, I managed to get it to work so thanks for that.

thanks for all your help.

"Max" wrote:

Pl do not email

Post a link to your sample* file here.

This way, the ensuing discussions
stays visible to all who may be interested.

You could use this to upload:
http://www.freefilehosting.net/

*desensitized as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On Fri, 1 Feb 2008 06:02:01 -0800, Zak wrote:

But i need to have the other references in there too..

=SUMIF(B2:B122,C123,D2:D122)

where would i insert your 'countif' bit in?

If this works then i need not post my sample file but if it doesnt i will.

thanks.

We have a language problem.

You asked: "... i would like to modify the formula
so it only COUNTs the negative numbers"

You don't write where these negative numbers are located.

In Excel, COUNT has a specific meaning. It generally adds 1 to a sum for each
value that meets a certain criteria, and there are multiple COUNT functions.
(COUNT; COUNTA; COUNTBLANK; COUNTIF; DCOUNT; DCOUNTA; COUNTIFS(in Excel 2007);
etc.)

If the Excel use of the word COUNT does not describe what you want to do, then
you had best post what you want to do in more detail.

The formula I gave will add 1 for each number in "rng" that meets your stated
criteria of being less than "0".

To COUNT the numbers less than zero in any range, merely substitute that range
for the "rng" token in the formula I gave.

=countif(D2:D122,"<0") will COUNT all the values in D2:D122 that are less than
zero.

As I wrote in my first post, "perhaps you want to do something else"?

If for example, you wanted to SUM all the values in D2:D122 that meet multiple
criteria, then you should write that, AND ALSO **ALL** the criteria you wish to
use.

One thought is that you want to SUM (or add up) all of the values in the range
D2:d122 that are less than zero, provided that the value in the same row in the
range B2:B122 matches the value in C123?

If that were the case, then you might try:

=SUMPRODUCT((B2:B122=C123)*(D2:D122<0)*D2:D122)

Perhaps you want to only COUNT those values?

Perhaps something else?

Stating your problem clearly allows those responding to avoid giving useless
answers in an attempt to read your mind, and saves a lot of time.

I often find that stating the problem clearly often enables ME to figure out
the answer without even asking for help.
--ron

Thanks for the extensive response, i didnt realise i was being so vague!
thanks for advise and thanks for formula, it works great.

very much appreciated

"Ron Rosenfeld" wrote:

On Fri, 1 Feb 2008 06:02:01 -0800, Zak wrote:

But i need to have the other references in there too..

=SUMIF(B2:B122,C123,D2:D122)

where would i insert your 'countif' bit in?

If this works then i need not post my sample file but if it doesnt i will.

thanks.

We have a language problem.

You asked: "... i would like to modify the formula
so it only COUNTs the negative numbers"

You don't write where these negative numbers are located.

In Excel, COUNT has a specific meaning. It generally adds 1 to a sum for each
value that meets a certain criteria, and there are multiple COUNT functions.
(COUNT; COUNTA; COUNTBLANK; COUNTIF; DCOUNT; DCOUNTA; COUNTIFS(in Excel 2007);
etc.)

If the Excel use of the word COUNT does not describe what you want to do, then
you had best post what you want to do in more detail.

The formula I gave will add 1 for each number in "rng" that meets your stated
criteria of being less than "0".

To COUNT the numbers less than zero in any range, merely substitute that range
for the "rng" token in the formula I gave.

=countif(D2:D122,"<0") will COUNT all the values in D2:D122 that are less than
zero.

As I wrote in my first post, "perhaps you want to do something else"?

If for example, you wanted to SUM all the values in D2:D122 that meet multiple
criteria, then you should write that, AND ALSO **ALL** the criteria you wish to
use.

One thought is that you want to SUM (or add up) all of the values in the range
D2:d122 that are less than zero, provided that the value in the same row in the
range B2:B122 matches the value in C123?

If that were the case, then you might try:

=SUMPRODUCT((B2:B122=C123)*(D2:D122<0)*D2:D122)

Perhaps you want to only COUNT those values?

Perhaps something else?

Stating your problem clearly allows those responding to avoid giving useless
answers in an attempt to read your mind, and saves a lot of time.

I often find that stating the problem clearly often enables ME to figure out
the answer without even asking for help.
--ron

On Fri, 1 Feb 2008 06:41:02 -0800, Zak wrote:

Thanks for the extensive response, i didnt realise i was being so vague!
thanks for advise and thanks for formula, it works great.

very much appreciated

Glad to help.

And vague requests are pretty common here. Frequently it takes several
iterations before some of us can understand what, I'm sure, initially seemed
perfectly clear to the original poster.
--ron

For some strange reason, I misread your
.. i tried if<0 but no luck.

and suggested this earlier:
=SUMPRODUCT((B2:B122=C123)*(D2:D1220)*D2:D122)

The condition should have been:
D2:D122<0

I'm sorry for the error.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---