COUNTIF with non-continuous range
 

Can I use the COUNTIF function on an area that does not have a continuous
range?

Example:

=COUNTIF(AG4:AG25,AA4:AA26,U4:U26,B4:B26,H4:H26,N4 :N26,AZ4:AZ25,AT4:AT25,AN4:AN25,1)

Generates an error - too many arguments. I have also tried it with quotes:

=COUNTIF("AG4:AG25,AA4:AA26,U4:U26,B4:B26,H4:H26,N 4:N26,AZ4:AZ25,AT4:AT25,AN4:AN25",1)

But this, too, generates a general error.

Suggestions? I have a complex spreadsheet and I need to count values all
over it.

COUNTIF with non-continuous range
 

Modify to suit

=SUMPRODUCT(COUNTIF(INDIRECT({"p2:p6","r2:r6"}),"= 1"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ken" wrote in message
...
Can I use the COUNTIF function on an area that does not have a continuous
range?

Example:

=COUNTIF(AG4:AG25,AA4:AA26,U4:U26,B4:B26,H4:H26,N4 :N26,AZ4:AZ25,AT4:AT25,AN4:AN25,1)

Generates an error - too many arguments. I have also tried it with
quotes:

=COUNTIF("AG4:AG25,AA4:AA26,U4:U26,B4:B26,H4:H26,N 4:N26,AZ4:AZ25,AT4:AT25,AN4:AN25",1)

But this, too, generates a general error.

Suggestions? I have a complex spreadsheet and I need to count values all
over it.

COUNTIF with non-continuous range
 

As long as the criteria is *numeric* you can use a formula like this:

=INDEX(FREQUENCY((A3:A5,C3:E4,H6:H9),x),n)

x and n depend on what the range of values are. Are the numbers all
integers?

FREQUENCY performs a series of "COUNTIFs" based on x. x can be more than one
value. In your case you want to count 1s.

=INDEX(FREQUENCY((A3:A5,C3:E4,H6:H9),1),1)

However, this will count *all* numbers <=1. So, we need to know what the
range of numbers are then we can tweak x and n to get the correct result.

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Can I use the COUNTIF function on an area that does not have a continuous
range?

Example:

=COUNTIF(AG4:AG25,AA4:AA26,U4:U26,B4:B26,H4:H26,N4 :N26,AZ4:AZ25,AT4:AT25,AN4:AN25,1)

Generates an error - too many arguments. I have also tried it with
quotes:

=COUNTIF("AG4:AG25,AA4:AA26,U4:U26,B4:B26,H4:H26,N 4:N26,AZ4:AZ25,AT4:AT25,AN4:AN25",1)

But this, too, generates a general error.

Suggestions? I have a complex spreadsheet and I need to count values all
over it.