Countif cell contents match within a range?

Question

Countif cell contents match within a range?

Hello Numbers Community- I've ditched MS Excel and it's fun (and a little awkward).

Goal: Create a function that counts the text content of a range within a certain cell, count it.

Example: A2 contains 'Orange'. I want to be able to create a function that will look in all of column B to count how many times 'Orange' appears.

In numbers, I can do this: COUNTIF(Data::AllData::B,"=Orange") but I have to type 'Orange' into the formula. I need to lookup over 100 strings. In excel, it was pretty easy, countif(A1=B:B)

What is the function to have numbers lookup the string in a cell and then count when found in a range?

Tried this without luck: COUNTIF(MATCH($A2,Data::AllData::B))

Thanks!!

Posted on Nov 3, 2021 9:48 AM

Reply

Answer 1

Barry

Level 9

60,023 points

Nov 3, 2021 10:34 AM in response to AnotherUserNameHere

Have you tried:

The formula shown below the table is entered in B2, then filled down to B10.

The test range reference ( A ) defines the range as 'all cells in column A of 'this table', except those cells in Header rows or Footer rows.

The single cell range ( A2 ) identifies the cell containing the test value (Orange).

Note that if no comparison operator is stated, = is assumed.

You can also place the formula, and the values to be counted, in separate table:

The range defined by a column-only reference will automatically omit or include the cells in header rows and footer rows depending on the function in which the reference is placed. These cells are omitted where the function is one usually placed in a header or footer row to COUNT or SUM values in the column containing the formula. The omission prevents a 'self-reference' error.

MATCH is a lookup function that returns the position in a list of the value being searched for.

You can find a description, an example, and usage notes for every function supported in your version of Numbers in the Function Browser. The browser opens in your right sidebar whenever you delect a cell and press = to open the Formula editor. The browser is searchable, and can be grouped in categories to show related functions.

Regards,

Barry

Reply

Answer 2

Nov 3, 2021 11:58 AM in response to Barry

Wow! I'm ashamed I even asked. You totally nailed it. I've spent over an hour trying to do that and somehow couldn't get it to work. Thank you!!! This is a huge help to me.

Thank you again! Nailed it :).

Reply

Answer 3

Barry

Level 9

60,023 points

Nov 3, 2021 12:22 PM in response to AnotherUserNameHere

"Wow! I'm ashamed I even asked."

There's no shame in needing some information and asking a question to get it.

Happy to have helped.

Regards,

Barry

Reply