How can I use INDEX and MATCH with COUNTIFS to automatically locate my test values collumn?

Question

Level 1

13 points

How can I use INDEX and MATCH with COUNTIFS to automatically locate my test values collumn?

Using Numbers 4.0.5,

I'm trying to summarize survey results that are not numerical. I am trying to separate results based on results like age, gender, job function, and community. I've been using COUNTIFS to separate the results, with good success. This is a tedious process and every time I want to make a change I have to go back in and change every single cell of code. I need a more foolproof system. What I would like to do is have an INDEX and MATCH function that can take the criteria I'm searching for, locate the column that needs to be searched, and return the number of matched criteria.

What I think should work is COUNTIFS(INDEX('Survey Results Table', 0, MATCH('Survey Results Table Header Row', [cell with search criteria], 0)), [cell with search criteria within the column])

For example, if I'm searching for Community:

COUNTIFS(INDEX('Survey Results Table', 0, MATCH('Survey Results Table Header Row', ["Where do you live?"], 0)), ["Calgary"].

Thoughts? Am I on the right track? Is this even possible?

Posted on Feb 21, 2017 9:37 AM

Reply

Answer 1

SGIII

Level 8

37,538 points

Feb 21, 2017 6:50 PM in response to gramdaman

Is something like this what you are looking for to reduce the drudgery?

The formula in cell B4:

=COUNTIFS(OFFSET(Data::A,0,MATCH(B2,Data::1:1,0)−1),B3)

You can input values you want into B2 and B3 of the 'Count' table and the formula in B4 will do a count of how many times the specified value occurs in the specified column.

The MATCH figures out what column number, then feeds that (subtracting 1) to a special form of OFFSET that has as its base (the first parameter) not a single cell but the entire column A of the 'Data' table. So when OFFSET receives the column offset number (from MATCH) it knows you mean the entire column D. The COUNTIFS is then performed on that entire column.

You can also do this with INDEX in cell B4, as follows:

=COUNTIFS(INDEX(Data::A1:D6,0,MATCH(B2,Data::1:1,0)),B3)

Unlike with OFFSET you don't need to subtract 1. But you need to reference all the columns of the 'Data' table, not just Column A.

SG

Reply

Answer 2

Feb 21, 2017 10:00 AM in response to gramdaman

countif will certainly work

I do not think you should need index() since the countif() will operate on a column of data.

Posting an example off the data (even pasting the data table in the forums) will help us help you.

Reply