1

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

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

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • 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
    Commented Aug 15, 2017 at 6:33
2

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

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

Your Answer

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

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