Check another column to determine the kind of calculation to perform

Question

I have a spread sheet which, among other things, has a table with a row that specifies the type of problem (put in numbers 0 to 4, according to a reference table) that occurred, and the column next to it says how long it took to be solved. The things is, the problems are not sorted by type, so I can't perform a regular SUM, MEDIAN, etc... on a range. It looks something like this:

Problem type | Time taken 
      0      |     143 
      3      |     123 
      2      |     987 
      0      |     431

And so on. The options I see are either composing 5 new tables by error type, or make

= function IF (*totalSumRange*; "cellToTheLeft == errorTypeNumber")

kind of statements, but I apparently can't specify such things within the arguments.

Answer 1

You can use AVERAGEIF in Google Sheets, per below. (Similarly for SUMIF.)

You can either specify the exact range, as above (A$2:A$8), or generalise the parameters to A:A, D2, B:B. I prefer the latter as the formula doesn't need to be updated when new entries are added to columns A and B. However, this assumes there is no other data further down in columns A and B.

For reference, from Google Docs Editors Help:

AVERAGEIF(criteria_range, criterion, [average_range])

• criteria_range - The range to check against criterion.
• criterion - The pattern or test to apply to criteria_range.
  • Equals: "text" or 1 or "=text" or "=1"
  • Greater than: ">1"
  • Greater than or equal to: ">=1" Less than: "<1" Less than or equal to: "<=1"
  • Not equal to: "<>1" or "<>text"
• average_range - [ OPTIONAL ] - The range to average. If not included, criteria_range is used for the average instead.