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


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

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

2 Answers 2


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


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:


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.

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.