CountIF Help (Excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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%5B/url%5D

Steve

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote