# What do A:A and !A:A mean?

I have come across this SUMIF formula, I can't find any help doing a Google search.

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

So, from what I understand of SUMIF, as per the documentation:

``````SUMIF(range, criterion, [sum_range])
``````

But I don't understand fully the `N:N`.

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

Also please explain `!A:A`

• @pnuts Ah, OK. I didn't know that's how it worked. Great job, thanks very much! Aug 14, 2017 at 1:43

In this context the `!` means nothing (is meaningless) in isolation. Try:

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

and this will return a formula parse error.

However when referring to ColumnN (as with `N:N`) in a different sheet the exclamation mark is required. So in say Sheet2 the following is valid:

``````=sumif(Sheet1!N:N,"Cat",C:C)
``````

`Sheet` and `SheetN` are valid sheet names and without a delimiter of some kind the software would not know whether something like `SheetNN1:OR1` referred to range `N1:OR1` in `SheetN` or range `NN1:OR1` in `Sheet`.

• Thank you, so the exclamation is a delimiter and not an operator (not). Coming from coding can cause a few issues with this stuff :-) Aug 15, 2017 at 6:33

N:N just means the whole of column N, and likewise for A:A.

• @TinoMclaren Can you give some context, like a formula in which it appears, and what result the formula gives. Jul 13, 2017 at 17:25