2

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

enter image description here

(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?

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:

setup

and the result will be

result


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.