1. ## 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. ## 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.

3. ## Re: Another COUNTIF Problem

That did the trick.

Thanks!

4. ## 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.

#### Posting Permissions

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