Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Antwerp, Vlaanderen, Belgium
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •