Sum if

[Harvey Waxman]

I have a column of percentages with lots of places beyond the decimal point. I
want to count the number of occurrences of a single percentage, say 5%.

If I could round up all the values to only 2 decimal places (.05 for example)
it would be easy enough.

Instead i tried this but got a value of 0 which isn't quite true.

{=SUM(IF(A1:A98,">5%",IF(A1:A98,"<6%")))}

Can someone help?

 

[JE McGimpsey]

I have a column of percentages with lots of places beyond the decimal point.
I
want to count the number of occurrences of a single percentage, say 5%.

If I could round up all the values to only 2 decimal places (.05 for example)
it would be easy enough.

Instead i tried this but got a value of 0 which isn't quite true.

{=SUM(IF(A1:A98,">5%",IF(A1:A98,"<6%")))}

Can someone help?

One way:

=SUMPRODUCT(--(ROUNDUP(A1:A98,2)=0.06)))

another:

=COUNTIF(A1:A98,">0.05") - COUNTIF(A1:A98,">0.06")

 

[Dr. Harvey Waxman]

One way:

=SUMPRODUCT(--(ROUNDUP(A1:A98,2)=0.06)))

another:

=COUNTIF(A1:A98,">0.05") - COUNTIF(A1:A98,">0.06")

Thank you once again

 

[Harvey Waxman]

One way:

=SUMPRODUCT(--(ROUNDUP(A1:A98,2)=0.06)))

another:

=COUNTIF(A1:A98,">0.05") - COUNTIF(A1:A98,">0.06")

By the way, is there a rule to know how to express the criteria? For example,
COUNTIF needs ,">0.06" but IF needs >.06.

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(ROUNDUP(A1:A98,2)=0.06)))

another:

=COUNTIF(A1:A98,">0.05") - COUNTIF(A1:A98,">0.06")

By the way, is there a rule to know how to express the criteria? For
example,
COUNTIF needs ,">0.06" but IF needs >.06.[/QUOTE]

Not really - you could use ">0.06", ">.06", ">6%", etc. for either
function.

XL will translate each of them to the binary machine representation
(IEEE-754 floating point double):

00111111101011110101110000101000111101011100001010001111010111000

 

[Harvey Waxman]

COUNTIF needs ,">0.06" but IF needs >.06.

Not really - you could use ">0.06", ">.06", ">6%", etc. for either
function.[/QUOTE]

I get an error if I use quotes with the criterion in an IF statement. (see
original question)

I'm trying to discern when it is necessary to surround the criteria with quotes
and when it isn't - i.e. which functions need what syntax.

 

[JE McGimpsey]

I get an error if I use quotes with the criterion in an IF statement. (see
original question)

I'm trying to discern when it is necessary to surround the criteria with
quotes
and when it isn't - i.e. which functions need what syntax.

Take a look at XL Help. COUNTIF and SUMIF are functions which can take
constants, cell references or expressions. Expressions need to be text
strings, so they can be of the form

">0.06"
"<.2"
">=" & SUM(A1:A10)

etc.

OTOH, with IF, the comparison argument has to evaluate to a boolean TRUE
or FALSE (for which XL interprets a numeric, non-zero value as TRUE and
a numeric value of zero as FALSE).

Since ">0.06" doesn't evaluate to either a boolean or numeric value,

=IF(">0.06","Yes","No")

will return #VALUE!

Internally, in XL's evaluation of the formula

=IF(A1>0.06,"Yes","No")

the criterion A1>0.06 is evaluated *before* it's sent to the IF
function. Only its result (TRUE/FALSE) is passed to IF.

 

[Harvey Waxman]

the criterion A1>0.06 is evaluated *before* it's sent to the IF
function. Only its result (TRUE/FALSE) is passed to IF.

Thank you

 

[ShaneDevenshire]

Hi Harvey,

First thing your formula is not complete: IF(Test,True,False). It appears
you are trying to work with items between 5% and 6%, in effect you want a
formula like:

=SUM(IF((A1:A9>=5%)*(A1:A9<6%),1))

I have left out the false part because you can (or you can write it:

=SUM(IF((A1:A9>=5%)*(A1:A9<6%),1,0))

Both of these formulas are entered as arrays.

You can also enter the non-array formula:

=SUMPRODUCT((A1:A9>=5%)*(A1:A9<6%))
or
=SUMPRODUCT(--(A1:A9>=5%),--(A1:A9<6%))

 

[Harvey Waxman]

Super, many thanks

First thing your formula is not complete: IF(Test,True,False). It appears
you are trying to work with items between 5% and 6%, in effect you want a
formula like:

=SUM(IF((A1:A9>=5%)*(A1:A9<6%),1))

I have left out the false part because you can (or you can write it:

=SUM(IF((A1:A9>=5%)*(A1:A9<6%),1,0))

Both of these formulas are entered as arrays.

You can also enter the non-array formula:

=SUMPRODUCT((A1:A9>=5%)*(A1:A9<6%))
or
=SUMPRODUCT(--(A1:A9>=5%),--(A1:A9<6%))