Compound COUNTIF()?

An "easy" way is to concatenate the two columns values then count on the new column. See below:



Regards,

Pivot Tables would make this type of question easy to answer.

Regards,

I think the "members" of this forum have convinced themselves that with the current facilities available in Numbers, adding a column is the only solution. Remember, though, this doesn't need to be visible; the column can be hidden or squirreled away in a separate table on another sheet.

The second argument to COUNTIF() is not "rich"; it needs to be an expression that evaluates, oddly, to a string complying to a restricted syntax. All the major spreadsheets, Excel included, I've used have this same restriction (in fact, I suspect Numbers COUNTIF() functionality is taken directly from Excel for compatibility reasons). Excel does, though, provide an additional feature, arrays, that is often used to work around this deficiency in COUNTIF(), but that is not available in Numbers.

Hello

Just a question.

Is Yvan the first name of WWJD ?

Yvan KOENIG (from FRANCE vendredi 30 novembre 2007 15:16:16)

That is odd, considering you had not responded to this post yet WWJD, I mean 5|==v.. I mean Yvan. 🙂

see ya' round,
James.. no wait thats my two year old..

Jason.. thats me

Hi, like the idea presented above by WWJD. However it seems you may be able to do it without needing the extra line of data ( or even a hidden one) I may not be right, but I tried it out and it worked for me.
=IF(AND(COUNTIF(B3:B5,"yes")),(COUNTIF(C3:C5,"no")))

With this formula you can see how many rows contain BOTH a 'yes' in column B AND a 'no' in column C.

Could someone explain how to import a screen shot? It would make showing this a lot easier!

Hope this is what you need!

KEANDAU wrote:
...
=IF(AND(COUNTIF(B3:B5,"yes")),(COUNTIF(C3:C5,"no")))

I don't think this is going to get you anywhere. Here is a paraphrase of this formula:

If there are 1 or more "yes"s in the range B3:B5, then the result is the number of "no"s in the range C3:C5 else the result is "FALSE".

If this appears to be working, it is only because of the nature of the test data you are using.

Am I revealing my age? :O

Regards,

Hi Chris,
Yeah that original post I made only worked because of the supplied data not because the formula was right. I did some research on "Excel" and found this:

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

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. This does not work at all in Numbers.

I tried saving the Excel file and then converting it to Numbers but Numbers said it could not recognize the formula.
Perhaps if you have Excel (or someone else reading this) you could give it a try or build on this knowledge. To access the wizard in Excel go to Tools/Wizard/Conditional Sum.

Sorry Chris thats the best that I have. Too little experience 😟

Kevin B.