Limited use of =Countifs ?

[Captain Randy]

When working with =Countifs(B1:B52,B60,C1:C52,D61)
Is there a limlit as to how many rows and columns you can use for this Formula?

If not what kind of simple mistake seems to be the most common ones made?

This formula works great until I get more then three items typed in.
(items being; B1:B52,B60) B1:B52 look in column, B60 looking for this item in B1:B52.

Any help from some of the pros would sure be "WELCOME"

Thank You in Advance
Randy,

 

[barry houdini]

Hello Randy,

There's no limit on rows - you can even use the entire column, e.g.

=COUNTIFS(B:B,B60,C:C,D61)

which version is giving you trouble? Note that all conditions must be true for the row to be counted......

 

[T. Valko]

When working with =Countifs(B1:B52,B60,C1:C52,D61)
Is there a limlit as to how many rows and columns you can use for this Formula?

If not what kind of simple mistake seems to be the most common ones made?

This formula works great until I get more then three items typed in.
(items being; B1:B52,B60) B1:B52 look in column, B60 looking for this item in B1:B52.

Any help from some of the pros would sure be "WELCOME"

Thank You in Advance
Randy,

You can have up to 127 range/criteria pairs.

Post the full formula that you're having trouble with.

 

[Captain Randy]

Hello Barry Houndini
I'm working with Excel 2007.
There are 13 columns, A through M
Let me outline this for you.
In Column A1:A52 there are 52 items some are repet some not.
I'm looking for item xx in column a1:a52 and then in column B B1:B52 looking for a smybol of an angle and then in column C1:C52 looking also for anther zzz and if they are in this spreadsheet I wanted it to return how many there are.
Like I said it works great until I get past 5 columns. Then it acts as if it can not fine the rest in the other columns.

"You did say that conditions must be true" the condition is IF the items are in there Columns and across the rows, it should return and give a number as to how many items was found.
I can see the items in there, but I can not get Excel to count them.

This brings me back, perhaps it is the opeperater at error.
I hope this helps you understand where I'm at.

I'll say this if this sheet was on your screen, you would most likely see what and why it is acting the way it is without any thinking.

Thank You very much for your help.
Randy

 

[Captain Randy]

Hello T. Valko
Seems that we have been meeting on the last few days.. Nice to hear from you again.
Ok put your eagle eyes on here and see what I have done wrong.

=COUNTIFS($B$1:$B$52,"A71",$C$1:$C$52,"$C$70",$D$1:$D$52,"K71",$F$1:$F$52,"L70",$F$1:$F$52,"M$70")

Thank You
Randy

 

[chuckles1066]

I'm probably wrong but the cell references shouldn't be enclosed with ""?

 

[T. Valko]

Hello T. Valko
Seems that we have been meeting on the last few days.. Nice to hear from you again.
Ok put your eagle eyes on here and see what I have done wrong.

=COUNTIFS($B$1:$B$52,"A71",$C$1:$C$52,"$C$70",$D$1:$D$52,"K71",$F$1:$F$52,"L70",$F$1:$F$52,"M$70")

Thank You
Randy

Ok, a couple of things...

If the criteria is a cell reference don't quote it.

You're also testing the same range twice for different criteria:

$F$1:$F$52,"L70"
$F$1:$F$52,"M$70"

The cells in that range can not be equal to both criteria at the same time.

 

[Captain Randy]

Ok T Valko
I will go to work on that, and see what I can get done.
Thank You very much for your input..
Have a great day.
Randy

 

[T. Valko]

Ok T Valko
I will go to work on that, and see what I can get done.
Thank You very much for your input..
Have a great day.
Randy

Just let us know if you get stuck!

 

[Captain Randy]

Hello T Valko
You said in your last post,
"The cells in that range can notYou're also testing the same range twice for different criteria:

$F$1:$F$52,"L70"
$F$1:$F$52,"M$70"

be equal to both criteria at the same time." <!-- / message --><!-- sig -->

Let me ask you, once all of the criteria's are meet, is there a WAY I can add what is on the same line that the criteria's are met ?
I attemped to add =countifs(And( in the front of the code, but that made of a mess.

Once I followed your advice and dropping the last criteria ($f$1:$f$52,L70)
Every thing works great!

But the last most important info is at the end of the criteria which comes from F row.
In your wisdom is there a way I can add this last row?

Thank You, very much for your help.
Randy