## Usage

### Referencing sheets

``````'sheet_name'!range
``````

Where ‘sheet_name’ is the name of the sheet as shown on its tab at the bottom.

E.g:

``````'Time'!A1:A100
``````

Also see Import data from another spreadsheet.

### List of items

``````unique(range)
``````

E.g. B1 has the formula `=unique(A1:A4)`:

A B
1 cat cat <- `=unique(A1:A4)`
2 dog dog
3 cat tortoise
4 tortoise

### Conditional sum

``````sumif(range, condition, sum_range)
``````

You must use double quotes not single quotes.

E.g:

A B
1 yes 3
2 no 2
3 yes 4

`=sumif(A1:A3,"yes",B1:B3)` == 7

### Complex conditional sum

``````=arrayformula(sumif(range_1&range_2, condition_1&condition_2, sum_range))
``````

E.g:

Animal Item Amount
cat food 3
dog toy 6
cat food 4
dog food 5
Animal Total food
cat 7 <- `=arrayformula(sumif(A12:A15&B12:B15,A18&"food",C12:C15))`
dog 5 <- `=arrayformula(sumif(A12:A15&B12:B15,A19&"food",C12:C15))`

### Import data from another spreadsheet

``````=importrange("spreadsheet_key", "sheet_name!range")
``````

‘sheet_name’ is the name of the sheet as shown on its tab at the bottom.

## Bugs

This is a list of bugs that I’ve experienced while using Google Sheets. It probably isn’t useful to anyone, but I wanted a record to check against if it’s behaving oddly in the future.

### Not interpreting formulas

The following formuls worked in one cell but not in another:

``````=unique('Time'!E2:E)
``````

In fact, after further exploration, it seems no new formulas are interpreted. In order to have it work again I had to close the spreadsheet and open it again.

### Infinite warning popups

Trying to create a pie chart and adding an invalid range, the warning popup “The range you specified is not in a valid range format” would not stop appearing, rendering the page useless, preventing me navigating away from the page and very nearly forcing me to kill the browser.

### Inconsistent results

Using an identical formula in two different cells generates different results. The formula is in a ‘Summary’ sheet and pulls data from a ‘Time’ sheet. The following two lines show the formulas, actually copy and pasted from the sheet:

``````=SUMIF(YEAR('Time'!A2:A), 2009, 'Time'!J2:J) -> 5
=SUMIF(YEAR('Time'!A2:A), 2009, 'Time'!J2:J) -> 0
``````

### Cell randomly losing formatting

I formatted a column to show numbers with 2 decimal places. One cell in the column keeps resetting to showing 13 decimal places.

### Random auto formatting

10 minutes of randomly resizing the columns to be tiny.