Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Orlando, FL, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Another COUNTIF Problem

    I have a workbook where the results of a questionnaire are stored on one sheet and summarized on another. I have been using the COUNTIF function to get grand totals for questions with "Y" and "N" responses, but I would like to write a formula to get totals by office. I tried a control array using the following formula:

    {=COUNTIF(IF(LEFT('SA Results'!A5:A1000,4)="1101",'SA Results'!$D$5:$D$1000,"Y"))}

    Where the office code is being parsed from column A and the responses are in column D, but I get an error message that I have entered too few arguements. Suggestions?

    Thanks!

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Another COUNTIF Problem

    Hi Tim,
    You should be able to use something like:
    {=SUM((LEFT('SA Results'!A5:A1000,4)="1101")*('SA Results'!$D$5:$D$1000="Y"))}
    to get what you need.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Orlando, FL, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another COUNTIF Problem

    That did the trick.

    Thanks!

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another COUNTIF Problem

    The CountIf function can not handle more than one condition, and you have two. You can accomplish what you are trying to do using an array formula like this:

    <pre>=SUM((LEFT('SA Results'!A5:A1000,4)="1101")*(UPPER('SA Results'!$D$5:$D$1000)="Y"))
    </pre>


    That is an array formula, so you must hold down the Shift and Ctrl keys when you press the Enter key to enter it into the cell. If you do that correctly, Excel will put curley brackets {} around the formula.
    Legare Coleman

Posting Permissions

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