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

1
  • 1
    @pnuts Ah, OK. I didn't know that's how it worked. Great job, thanks very much! – Ashton Aug 14 '17 at 1:43
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.

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 :-) – Tino Mclaren Aug 15 '17 at 6:33
2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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