# Thread: CountIF Help (Excel 97)

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

http://www.cpearson.com/excel/array.htm

Steve

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

#### Posting Permissions

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