How do I count the number of rows that meet multiple conditions?

MM2,

COUNTIFS will do that for you.

Jerry

Hi MiniMe,

The AND function will do it.

C2 in the first table contains this formula (anf Fill Down)

=AND(A2='Table 1-1' :: A$1,B2<'Table 1-1' :: B$1)

The count the number of rows containing TRUE.

Regards,

Ian.

Hi 'Me2,

You wrote:

"I had also been experimenting with the concatenation operator but was having some difficulty. I didn't see any examples in the manual with concatenation the way you illustrated above."

There's no concatenation in Ian's examples, illustrated below:

Neither the concatenation operator ( & ) nor the CONCATENATE() function appears in that formula.

AND returns TRUE only if ALL of the comparisons in its list return TRUE. In Ian's formula, there are two conditions statements: The name in column A must match (exactly) the value in cell A1 of Table 1-1 (the text sting "James", AND the year number in column B must less than the value in B1 of Table 1-1. If either of these is not TRUE, then AND returns FALSE.

For the three FALSE results above, the second comparison returns FALSE in row 2, the first returns FALSE in row 5, and both return FASE in row 7.

"once I get your idea working (currently it's always producing FALSE in my more complex spreadsheet for reasons I don't understand yet)"

Try inserting a new column for each comparison included in the AND statement, then testing each one independently in its own column. Here's an example, Using the data and formula in Ian's table above.

"but there is something very curious about your idea. You are passing an array to the AND function."

Not true.

I'm curious as to where you think Ian's formula is referencing an array. here's the formula from Ian's example:

=AND(A2='Table 1-1' :: A$1,B2<'Table 1-1' :: B$1)

Regards,

Barry

Hi MiniMe,

Jerry's solution using COUNTIFS is the way to go. My solution using AND (with another column in your first table) requires another formula in Table 2 to count all the rows in Table 1 that contain TRUE. That will require a COUNTIF to count all the TRUE values.

Doable, but the long way around.

There is more than one way to skin a cat. Some ways are quicker than others. Skin your cat with Jerry's method

Regards,

Ian.

Hi MiniMe,

No worries. The AND function can be useful. Please reply with your more complex question and maybe someone in this forum will be able to help.

Looking back to your original post, I now suspect that you are looking for multiple combinations of names and years of birth from Table 1 to Table 2.

Regards,

Ian.

MM2,

The COUNTIFS solution is inherently scalable. You may have as many conditions as you like for a given row, and these can be multiple tests on the same column or on different columns. For instance, you could test for column b content of greater than 1970 and less than 1975. Each requirement is another pair of Column&Condition cases.

Post your entire problem statement and we'll help you work it out.

Jerry

Hi MiniMe,

I am able to break down my 10,000 data items into about ten separate tables. I believe that should prevent too much bogging down. Once my table is built I won't be editing it a lot, so I'm okay with this performance issue.

That looks like a good idea. A separate COUNTIFS table for each Names table means the formula will have a smaller list to search.

Another way is to split a document into two or more smaller ones. If it suits your purpose, you could sort your "table of names" alphabetically by name. Then all the Aarons to Michaels could go into one document and all the Nigels to Zaccarias into another. Not as convenient, because Numbers will not link between documents.

In Jerry's example, the COUNTIFS formula is in Table 2 and allows for one name-age combination. If your Table 2 is building a list of name-age combinations, you could Copy then Paste Formula Results to get text instead of formulas. Maybe that will help with speed and document size. Will need updating when you edit the Names table.

Let us know how you go.

Regards,

Ian.

Hi MiniMe,

=AND(A2='Table 1-1' :: A$1,B2<'Table 1-1' :: B$1)

Not arrays, but cell references. The double colon :: comes between the table name and the cell reference.

The first test-expression:

A2='Table 1-1' :: A$1

A2 is the name in Row 2. This becomes A3... when filled down

Does this equal the contents of cell A1 in table 1-1?

The $ keep this reference fixed to Row 1 of Table 1-1 as the formula is filled down.

comma before the next test-expression:

B2<'Table 1-1' :: B$1

B2 is the year in Row 2. This becomes B3... when filled down

Is this less than the contents of cell B1 in table 1-1?

The $ keep this reference fixed to Row 1 of Table 1-1 as the formula is filled down.

My formulas are in the first table and refer to the second.

Jerry's COUNTIFS formula is in the second table and refers to the first.

The Numbers implementation of AND is strange, but the comma separator is shorter than typing: is this true AND is that true for a long list of comparisons.

Regards,

Ian.

MiniMe2 wrote:

I want to share some additional results. I want to report that when my "table of names" reaches about 1000 entries then Numbers bogs down fairly badly. So badly, that when I type a word in a cell, like "Jonathan" I look up to see if the word has been typed and only the "J" has been typed, I watch as it slowly spells out "o..n..a..t..h..a..n". The reason for this is that my other table already contains 400 cells that have to be recalculated every time I modify a cell in my table of names. But for me that's not a big deal. I'm up to 1275 rows now, eventually I would reach about 10,000, but I have one trick up my sleeve: in my situation I am able to break down my 10,000 data items into about ten separate tables. I believe that should prevent too much bogging down. Once my table is built I won't be editing it a lot, so I'm okay with this performance issue.

This to me is what scalability is about. It's either the inherent nature of the COUNTIFS statement or perhaps an implementation defect that causes Numbers to exponentially slow down as the table grows. So I would say don't use COUNTIFS if your table goes much beyond 1000 lines, unless your table doesn't change very often, in which case the limit is higher.

Numbers 2 is notoriously slow, and to add to the pain, it is notoriously dumb about when i recalculates.

If you keep TextEdit handy, you can create your entries there and Paste into Numbers as whole words or phrases to reduce the number of recalculations.

Jerry