IF statement greater than and less than

  • Thread starter Thread starter Potsy
  • Start date Start date
P

Potsy

hi

I would like to drop values into 5 columns AC-AH depending upon the
value entered into column N.

The ranges will be

<4999
5000-9999
10000-29999
30000-49999
50000-99999
100000
Click to expand...

I have successfuly done the first criteria based upon one argument
(less than 4999)

However, the next band i am struggling with the greater and less than
within IF - HELP please!!!

This is what I have but not working....

=IF(AND(N1626>=5000,<9999),N1626,"")

thanks in advance
 
Potsy said:
This is what I have but not working....
=IF(AND(N1626>=5000,<9999),N1626,"")
Click to expand...

All that is wrong is you are missing another reference to N1626. The
correct form is:

=IF(AND(N1626>=5000,N1626<=9999),N1626,"")

(Also note the change to "<=" 9999, which is consistent with your numerical
ranges.)

You can also write that in the following equivalent form:

=IF(AND(5000<=N1626,N1626<=9999),N1626,"")

I prefer that form because it is close to the standard mathematical form,
5000<=N1629<=9999.

One last comment: you might want to look at the VLOOKUP function and the
LOOKUP function. These are allow for comparing against many ranges without
having many nested IF() expressions.



----- original message -----
 
All that is wrong is you are missing another reference to N1626.  The
correct form is:

=IF(AND(N1626>=5000,N1626<=9999),N1626,"")

(Also note the change to "<=" 9999, which is consistent with your numerical
ranges.)

You can also write that in the following equivalent form:

=IF(AND(5000<=N1626,N1626<=9999),N1626,"")

I prefer that form because it is close to the standard mathematical form,
5000<=N1629<=9999.

One last comment:  you might want to look at the VLOOKUP function and the
LOOKUP function.  These are allow for comparing against many ranges without
having many nested IF() expressions.

----- original message -----










- Show quoted text -
Click to expand...

brilliant worked a treat thanks
 
All that is wrong is you are missing another reference to N1626.  The
correct form is:

=IF(AND(N1626>=5000,N1626<=9999),N1626,"")

(Also note the change to "<=" 9999, which is consistent with your numerical
ranges.)

You can also write that in the following equivalent form:

=IF(AND(5000<=N1626,N1626<=9999),N1626,"")

I prefer that form because it is close to the standard mathematical form,
5000<=N1629<=9999.

One last comment:  you might want to look at the VLOOKUP function and the
LOOKUP function.  These are allow for comparing against many ranges without
having many nested IF() expressions.

----- original message -----










- Show quoted text -
Click to expand...

one other questions which i am sure is easy....

Column <J> is Year, I want to be able to COUNTIF and SUMIF (2 seperate
fields) for years "2007", "2008", "2009" by looking at column <J> to
establish year and totalling column corresponding values in <AC> if
greater than 0.

This is what I have at moment but not working:

=COUNTIF(AND(AC:AC>=1,J:J="2007"),"")
=SUMIF(AND(AC:AC>=1,J:J="2007"),"")
 
one other questions which i am sure is easy....

Column <J> is Year, I want to be able to COUNTIF and SUMIF (2 seperate
fields) for years "2007", "2008", "2009" by looking at column <J> to
establish year and totalling column corresponding values in <AC> if
greater than 0.

This is what I have at moment but not working:

=COUNTIF(AND(AC:AC>=1,J:J="2007"),"")
=SUMIF(AND(AC:AC>=1,J:J="2007"),"")- Hide quoted text -

- Show quoted text -
Click to expand...

just tried conditional sum on add ins but seems tempremental....
 
Potsy said:
This is what I have at moment but not working:
=COUNTIF(AND(AC:AC>=1,J:J="2007"),"")
=SUMIF(AND(AC:AC>=1,J:J="2007"),"")
Click to expand...

First, does column J have numbers like 2007, 2008 etc (or dates formatted as
Custom yyyy), or does it have text like "2007", "2008" etc?

I will assume numbers in examples. Put quotes around any "number" that is
really text.

Second, it is not clear what you want to sum. I will presume the qualified
cells in column AC.

To count: =SUMPRODUCT((AC1:AC1000>=1)*(J1:J1000=2007))

To sum: =SUMPRODUCT((AC1:AC1000>=1)*(J1:J1000=2007),AC1:AC1000)

Alternatively, to sum (and similarly to count):

=SUMPRODUCT(--(AC1:AC1000>=1),--(J1:J1000=2007),AC1:AC1000)

Many people do not understand the purpose of "--" (double-negation). There
is no magic. It simply converts the boolean results (TRUE, FALSE) into
numbers (1, 0), which SUMPRODUCT requires, as does any arithmetic operation
like multiplication in the first form above.

If you have Excel 2007, you can use SUMIFS and COUNTIFS (note the "S")
instead.


----- original message -----

All that is wrong is you are missing another reference to N1626. The
correct form is:

=IF(AND(N1626>=5000,N1626<=9999),N1626,"")

(Also note the change to "<=" 9999, which is consistent with your
numerical
ranges.)

You can also write that in the following equivalent form:

=IF(AND(5000<=N1626,N1626<=9999),N1626,"")

I prefer that form because it is close to the standard mathematical form,
5000<=N1629<=9999.

One last comment: you might want to look at the VLOOKUP function and the
LOOKUP function. These are allow for comparing against many ranges without
having many nested IF() expressions.

----- original message -----










- Show quoted text -
Click to expand...

one other questions which i am sure is easy....

Column <J> is Year, I want to be able to COUNTIF and SUMIF (2 seperate
fields) for years "2007", "2008", "2009" by looking at column <J> to
establish year and totalling column corresponding values in <AC> if
greater than 0.

This is what I have at moment but not working:

=COUNTIF(AND(AC:AC>=1,J:J="2007"),"")
=SUMIF(AND(AC:AC>=1,J:J="2007"),"")
 
First, does column J have numbers like 2007, 2008 etc (or dates formattedas
Custom yyyy), or does it have text like "2007", "2008" etc?

I will assume numbers in examples.  Put quotes around any "number" thatis
really text.

Second, it is not clear what you want to sum.  I will presume the qualified
cells in column AC.

To count:  =SUMPRODUCT((AC1:AC1000>=1)*(J1:J1000=2007))

To sum:    =SUMPRODUCT((AC1:AC1000>=1)*(J1:J1000=2007),AC1:AC1000)

Alternatively, to sum (and similarly to count):

=SUMPRODUCT(--(AC1:AC1000>=1),--(J1:J1000=2007),AC1:AC1000)

Many people do not understand the purpose of "--" (double-negation).  There
is no magic.  It simply converts the boolean results (TRUE, FALSE) into
numbers (1, 0), which SUMPRODUCT requires, as does any arithmetic operation
like multiplication in the first form above.

If you have Excel 2007, you can use SUMIFS and COUNTIFS (note the "S")
instead.

----- original message -----










one other questions which i am sure is easy....

Column <J> is Year, I want to be able to COUNTIF and SUMIF (2 seperate
fields) for years "2007", "2008", "2009" by looking at column <J> to
establish year and totalling column corresponding values in <AC> if
greater than 0.

This is what I have at moment but not working:

=COUNTIF(AND(AC:AC>=1,J:J="2007"),"")
=SUMIF(AND(AC:AC>=1,J:J="2007"),"")- Hide quoted text -

- Show quoted text -
Click to expand...

great thanks sum product working great but count is just counting all
of the cells even if blank how come when =>1 is in formula?
 
Potsy said:
sum product working great but count is just counting all
of the cells even if blank
Click to expand...

It is hard to believe that the summing formula works, but not the counting
formula. They both use the same condition. In fact, they both work for me.
Check for typos. Better still: copy-and-paste from my original message
into your worksheet.

how come when =>1 is in formula?
Click to expand...

Because you wrote originally:

=COUNTIF(AND(AC:AC>=1,J:J="2007"),"")
=SUMIF(AND(AC:AC>=1,J:J="2007"),"")


----- original message -----

First, does column J have numbers like 2007, 2008 etc (or dates formatted
as
Custom yyyy), or does it have text like "2007", "2008" etc?

I will assume numbers in examples. Put quotes around any "number" that is
really text.

Second, it is not clear what you want to sum. I will presume the qualified
cells in column AC.

To count: =SUMPRODUCT((AC1:AC1000>=1)*(J1:J1000=2007))

To sum: =SUMPRODUCT((AC1:AC1000>=1)*(J1:J1000=2007),AC1:AC1000)

Alternatively, to sum (and similarly to count):

=SUMPRODUCT(--(AC1:AC1000>=1),--(J1:J1000=2007),AC1:AC1000)

Many people do not understand the purpose of "--" (double-negation). There
is no magic. It simply converts the boolean results (TRUE, FALSE) into
numbers (1, 0), which SUMPRODUCT requires, as does any arithmetic
operation
like multiplication in the first form above.

If you have Excel 2007, you can use SUMIFS and COUNTIFS (note the "S")
instead.

----- original message -----










one other questions which i am sure is easy....

Column <J> is Year, I want to be able to COUNTIF and SUMIF (2 seperate
fields) for years "2007", "2008", "2009" by looking at column <J> to
establish year and totalling column corresponding values in <AC> if
greater than 0.

This is what I have at moment but not working:

=COUNTIF(AND(AC:AC>=1,J:J="2007"),"")
=SUMIF(AND(AC:AC>=1,J:J="2007"),"")- Hide quoted text -

- Show quoted text -
Click to expand...

great thanks sum product working great but count is just counting all
of the cells even if blank how come when =>1 is in formula?
 
Back
Top