Compound COUNTIF()?

After much searching and trying of different means I'm at a loss, what's the correct way to express a compount COUNTIF() in which I only want to count the number of rows where Condition A and Condition B are both met?

I've tried this (and many variants):

=COUNTIF((Result Table :: 'Start Pos.',"="& $A2) & (Result Table :: End Pos., ">", 10))

The table that this resides in has an index column of values from 1 to n that define the $A values.

Does this make sense the way I'm asking it?

Regards
Chris

MacBook, Mac OS X (10.5.1)

Posted on Nov 29, 2007 5:09 PM

17 replies

5-vv

Level 3

685 points

Dec 1, 2007 6:33 AM in response to KEANDAU

KEANDAU wrote:
{=SUM(IF($D$53:$D$56="YES",IF($E$53:$E$56="NO",1,0),0))}

Yes... this is the array facility in Excel that I mentioned in my post, above. This workaround for the lame way COUNTIF() works is not available in Numbers.

I had two columns of data D53:D56 (Yes) and E53:E56 (No). I used the formula wizard to do a 'Conditional Sum'. THIS WORKED PERFECTLY IN EVERY SCENARIO (even using a combination of text and integers). The { } on the outside of the formula indicate that this is a 'conditional equation'.

Don't get too excited. I could not, in any way, make an adjustment to the formula by clicking in the formula bar. If I copy and paste this formula in another cell it does not work either! The only way to get the thing to work is by using the wizard in Excel.

Arrays in Excel are so "bolted-on" that you cannot even type them as you see them. There is a magic whiz-bang key combination (option-return, maybe... someone please correct me) that you use to commit the formula which causes the curly braces to appear and put the formula into "array mode".

keithdoc

Level 1

4 points

Dec 6, 2007 8:48 PM in response to WWJD

It's a lot of work, but your use of concatenate certainly will do the trick. I've used it to do the trick.

Great job of simplifying a way to work around the inflexibility of countif expressions.

Thanks!

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Compound COUNTIF()?