Thread: IF formula
View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Zak Zak is offline
external usenet poster
 
Posts: 144
Default IF formula

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