Using countif function with values in non-adjacent cells

Question

Level 1

8 points

Using countif function with values in non-adjacent cells

I'm trying to use countif function to do a conditional count for cells in non-adjacent rows. Below is a simple example of what I'm trying to do. The image shows that only the cells I want the count to be based on are highlighted. But instead of giving a result of 2 (2 of the 4 cells contain the value 3), it gives 0. I have tried various conditions but never get a correct result. What am I doing wrong?

Thanks in advance!

MacBook Pro 15″, macOS 12.6

Posted on Apr 4, 2023 6:24 PM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,550 points

Apr 4, 2023 8:06 PM in response to melzie

COUNTIF takes an array as the first argument. You are using + to add up different cells. Numbers doesn't interpret that as an array.

Normally you would just do something like this:

=COUNTIF(A,3)

But if you must explicitly exclude A2, A4, A6, A8 for some reason then you can do something like this:

=COUNTIF({A1,A3,A5,A7,A9},3)

If your region uses , as the decimal separator then it will look like this:

=COUNTIF({A1.A3.A5.A7.A9};3)

SG

Reply

Answer 2

melzie Author

Level 1

8 points

Apr 5, 2023 1:17 PM in response to Yellowbox

According to Apple's Numbers Reference Guide, COUNTIF expects a range OR a "collection" (non-adjacent cells). The problem was trying to get the right syntax for a collection. When I selected individual cells, the app inserted the + sign, not me. The answer is as posted by SG, using curly brackets. None of the examples in Apple's Numbers Reference Guide showed that.

Thanks for all the replies!

Reply

Answer 3

Yellowbox

Level 7

21,939 points

Apr 4, 2023 7:57 PM in response to melzie

Hi melzie,

COUNTIF expects a range

Formula in A8: COUNTIF(A1:A7,3)

Please reply with questions.

Regards,

Ian .

Reply