Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    CountIF Help (Excel 97)

    I'm trying to create a formula, but I'm not sure how to do this one...

    Can you use multiple criteria in a CountIF?

    Currently I have

    =COUNTIF('FDD Consolidator'!$W:$W,$A$2&$A4&B$2)

    But I'd also like to add the criteria of 'FDD Consolidator'$C$C = A1

    I know how to use Array's with SumIF's, but I'm dealing with Text, not numbers..

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIF Help (Excel 97)

    Array Formulas do work in counting text.

    Try array entering SUM(('FDD Consolidator'!$W:$W=$A$2&$A4&B$2)*('FDD Consolidator'$C$C = A1))

    How does it work - I found out from the here... Bob Umlas

    I am sure there are other here who could also help, but try the link for starters

  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 Help (Excel 97)

    Not in a COUNTIF,
    BUT,
    Arrays will work (ctrl-shift-enter to confirm). Though you can NOT use the whole column, you must use at most 1 cell less than the full column.

    =COUNT(IF(('FDD Consolidator'!$W1:$W65535=$A$2&$A4&B$2)*('FDD Consolidator'$C1$C65535 = A1),'FDD Consolidator'$C1$C65535))

    You can replace count with sum or any of the other stat functions (std, average, min, max). Change "*" to "+" to go from AND to OR. You can also add more criterion with extra "*Crit" statements

    Chip Pearson has excellent link:
    http://www.cpearson.com/excel/array.htm

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: CountIF Help (Excel 97)

    Well, I'm going to have to practice on this one!

    I have:

    =COUNT(IF(('FDD Consolidator'!$W$2:$W$448=$A$3&$A5&S$3)*('FDD Consolidator'!$C$2:$C$448=$A$1),'FDD Consolidator'!$C$2:$C$448)) (With Ctrl-Shift-Enter)

    And I should have a result of 2 for that cell, but it is returning 0. My only other option is to add columns to my FDD Consolidator sheet, which is what I was trying to avoid.

    Thanks for the help and the links. I'm going to have to work on this one to find out what I am doing wrong!

  5. #5
    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 Help (Excel 97)

    Could you post an example?

    I don't know if this is related, BUT:
    One thing to note is that the value looked at for $A5 will not change as the array is goes down. Each row in "W" will be checked against (essentially) $A$3&$A$5&$S$3. All will use the same values (This is because you never "copy" the formula, it is in 1 cell)

    Also. If the items in Col C are text, you need to use CountA not Count. Count only counts numbers.
    Steve

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: CountIF Help (Excel 97)

    I am posting an example. Usually the Report Sheet has 4 different sections using a variety of either column L or M on the FDD Consolidator tab.

    One thing to note, when I tried this with CountA it gave me back 385 (the number of rows I went down)

    I cannot use a Pivot Table to accomplish what i need to accomplish. You would have been able to see that If I hadn't deleted the rest of the report.
    Attached Files Attached Files

Posting Permissions

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