Results 1 to 6 of 6
Thread: CountIF Help (Excel 97)

20030214, 02:48 #1
 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..

20030214, 03:12 #2
 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

20030214, 03:16 #3
 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 (ctrlshiftenter 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

20030214, 03:43 #4
 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 CtrlShiftEnter)
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!

20030214, 10:42 #5
 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

20030214, 16:33 #6
 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.