Cheat sheet for advanced Excel formulas

# Concatenation 

Concatenate strings this way:

`=A1&” more text”`

# Absolute reference

By default, references in Excel are relative. For an absolute reference, prefix the column and row with "$"

`=$A$2`

# Reference a cell in another sheet

`=Sheet1!A1`

# COUNTIF and SUMIF

Count cells that match criteria:

`=COUNTIF(A2:A, ">1")`

Sum cell values if they match criteria:

`=SUMIF(A2:A, ">1", B2:B)`