On a spreadsheet what does A:A and !A:A mean?

user_425357 0
Viewed 18084

I have come across this sumif formula, I cant find any help doing a Google search on the N:N

=sumif(N:N,"Cat",C:C)

So I understand the SumIf, as per the docs:

SUMIF(range, criterion, [sum_range])

But I don't fully understand the N:N. Also please explain !A:A

Can someone provide an official explanation or point me to the docs.

spreadsheet
2 Answers

N:N means that the range includes the whole column N, rather than just certain cells (e.g. N1:N300). This allows a formula to reference all data in the column even if you add more rows later on.

The exclamation mark is explained here: What does an exclamation mark before a cell reference mean?

When entered as the reference of a Named range, it refers to range on the sheet the named range is used on.

For example, create a named range MyName refering to =SUM(!B1:!K1)

Place a formula on Sheet1 =MyName. This will sum Sheet1!B1:K1

Now place the same formula (=MyName) on Sheet2. That formula will sum Sheet2!B1:K1

Note: (as pnuts commented) this and the regular SheetName!B1:K1 format are relative, so reference different cells as the =MyName formula is entered into different cells.

user_28312090

A clear answer to N:N was given. For the rest.. The exclamation mark in !A:A just means that you refer to another spreadsheet in your workbook -> say you currently work in Sheet2 and want to get Data from Sheet1. for this you have to use Sheet1 !A:A

user_147785900
Related