# How can I make COUNTIF with the condition on the nearest column?

I'm using Google Spreadsheets to consume the data I got from survey. But I don't like the summary Google automatically constructed, I want to make more complex analytics.

So, I have two rows, and my goal is to count all the occurrences of "namestring" in column J for different values in column I

(Sorry for Russian language.) In column I (the list of taxi services) while in column J the most valuable factors for the responders.

So I want to have statistic about every taxi service, depending on different parameters, mentioned in column J.

Can I use Google Spreadsheet for that? Or I need to work in Excel for such a task?

There is a tool designed for this purpose (in both Excel and Google Sheets): pivot table reports. For example, suppose this is your data:

``````+---+-------------+-----------+
|   |      I      |     J     |
+---+-------------+-----------+
| 1 | Application | Factor    |
| 2 | Qwerty      | Price     |
| 3 | Asdf        | Wait time |
| 4 | Zxcv        | Price     |
| 5 | Zxcv        | Wait time |
| 6 | Qwerty      | Price     |
| 7 | Qwerty      | Wait time |
| 8 | Qwerty      | Price     |
| 9 | Asdf        | Wait time |
+---+-------------+-----------+
``````

Select the range shown above and click Data > Pivot table. Set up the table in this way:

and the result will be

For completeness, I'll show an approach with `countifs` since you mentioned it: it's generally more work for the same result, with a greater chance of error.

1. In cell K2 put `=sort(unique(I2:I))` to get a sorted list of distinct names present in column I (excluding its header).
2. In cell L1, `=transpose(sort(unique(J2:J)))` to get a similar list of factors, but arranged as a row instead of a column. This makes a table like the one above:

### Summary table

``````+---+--------+-------+-----------+
|   |   K    |   L   |     M     |
+---+--------+-------+-----------+
| 1 |        | Price | Wait time |
| 2 | Asdf   |       |           |
| 3 | Qwerty |       |           |
| 4 | Zxcv   |       |           |
+---+--------+-------+-----------+
``````

Then fill the rectangular block by entering this formula in L2 and extending:

``````=countifs(\$I\$2:\$I, \$K2, \$J\$2:\$J, L\$1)
``````

This counts the number of combinations of an application and a factor, so you'll get the same numbers as in the pivot table report.