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

Question

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?

Answer 1

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.