COUNTIF

[Guest]

I have a column with %'s. There are 2983 values. I want to do a countif
statement that shows the values >0% and <=10%. When I do a
countif(data!bc:bc,">0") there are 391. if I do a countif(data!bc:bc,"<=10")
it shows 2598. But there are only 6 values that are >0% and <10%. When I type
in (countif(data!bc:bc,">0<=10") it will give me 1. Any help on how to write
this formula to get me the count or number of values that are >0 and less
than <=10%.

Thank you

 

[Brady]

Help,

In don't think you can use COUNTIF with two criteria (I may be
wrong). Do a search on this newsgroup for "Countif with two criteria"
and I think you will find several answers. I believe SUMPRODUCT is one
valid alternative.

Brady

 

[Dav]

Try sumproduct

sumproduct((a2:a100>0)*(a2:a100<0.1))

the percents are still values between 0 and 1 so 10%=0.1

Regards

Dav

 

[Guest]

How would i use the sumproduct function?

 

[Bearacade]

Try this

=COUNTIF(data!bc:bc,">0%")-COUNTIF(data!bc:bc,">=10%")

If you want to count values that equals to 10%, take the equal sign
away before the 10%

HTH

 

[Guest]

If you need two or more criteria you can use array formulas to do the trick.
A formula like:

{=COUNT(IF((A22:A27>0)*(A22:A27<0.1),1),)}

may work for you. However there are a few important points regarding array
formulas;

First, you enter the formula by pressing the Ctrl^Shift^Enter keys in
combination. not just using the enter key. The brackets in the formula are
not keyed in, they appear automatically when the Ctrl^Shift^Enter combination
is used.

Second, multilple criteria can be used and should be contained in
parentheses. The * operator is used as a logical "AND" and the + operator is
a logical "OR" operation.

Third, array formulas need to be on finite ranges, I do not believe that
selecting entire columns will work as you do in your example, and

Finally, array formulas are very demanding on system resources. You may not
want to use them if you have a very large spreadsheet, but you should be OK
with a couple thousand rows.

Also, as a general rule, you should probably not use the percentage sign in
criteria like you did. I get more reliable results by entering decimal values
in criteria.

For more info, check the article on Chip Pearson's site at
http://www.cpearson.com/excel/array.htm

 

[RagDyeR]

You could try combining 2 Countif formulas:

=COUNTIF(BC:BC,">0")-COUNTIF(BC:BC,">10")
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



How would i use the sumproduct function?

 

[Guest]

Thank you. Great.

 

[Guest]

Why dont you write the formula as it is?
Sumif() and use as condition ;and(>0;<=.1) ->
=sumif(data!bc:bc,and(>0,<=.1))

 

[Andrew Taylor]

Probably because it's not valid syntax..

Why dont you write the formula as it is?
Sumif() and use as condition ;and(>0;<=.1) ->
=sumif(data!bc:bc,and(>0,<=.1))

 

[Guest]

You're right. Then do it in 2 steps: add 1 column "AND(a1>0;a1<=.1)" -> true
/ false
and the count the "trues" (or sum the % where the column is true)