Google-sheets – Check another column to determine the kind of calculation to perform

google sheetsworksheet-function

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.

Best Answer

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

Google Sheets AverageIf Example

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.
Related Topic