# Thread: COUNTIF , conditional but for complete column (Excel 2000)

1. ## COUNTIF , conditional but for complete column (Excel 2000)

Hi Woody's,

I have following question:

I have a column, say A:A, containing 2 letter country codes such as US or BE, this column is a named range (Country_col);
Next I have a column containing a named range (tf_100).

The value equalling the country code is in, say \$c\$20.

I would like to put a formula together that checks the value in \$c\$20, then for all the rows where the colum "Country_col" value = \$c\$20, count the number of 'x' values contained in column tf_100.

I have this:
=IF(Country_col=\$C\$20;COUNTIF(tf_100;100))
But this only works on row level, not on the complete column....

Any suggestions?

Much appreciated,

Erwin.

2. ## Re: COUNTIF , conditional but for complete column (Excel 2000)

I don't understand your description, but this type of formula does not work with entire columns. So define Country_col as A1:A65000 or something like that, and similar for tf_100.

3. ## Re: COUNTIF , conditional but for complete column (Excel 2000)

Try the array formula (confirm with ctrl-shift-enter):
=Count((Country_col = \$c\$20)*(tf_100=x),1))

Or even the non-array:
=SUMPRODUCT((Country_col = \$C\$20)*1,(tf_100=x)*1)

Country_col and tf_100 must be the same size (ie have the same number of rows)
x = is the value to compare in the column tf_100

The first could also be used to get the average (or min,max, stdev, etc) if you use something like (again confirm with ctrl-shift-enter)
=Average((Country_col = \$c\$20)*(tf_100=x),tf100))

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•