Calculating a sum, exceptions for specific values

  • Thread starter Thread starter RedBeard
  • Start date Start date
R

RedBeard

Utter newbie here so please forgive stupid questions.

I have a column with values that I would like to sum up, so far no
problem.

However, if someone enters a specific number, this cell is to be
ignored and excluded from the total sum.

Can this be done somehow?
 
RedBeard,

Array enter (Enter using Ctrl-Shift-Enter) a formula like

=SUM(IF(A1:A10<>10,A1:A10))

where 10 is the value that you want ignored.

A non-array entered formula would be

=SUMPRODUCT((A1:A10<>10)*A1:A10)


HTH,
Bernie
MS Excel MVP
 
Duh! (smacking forehead)

And, of course,

=SUMIF(A1:A10,"<>10")

HTH,
Bernie
MS Excel MVP
 
If by specific number yuo mean 425, for example, then =SUMIF(A1:A100,"<>425")
will do it. Is that what you mean?
 
If by specific number yuo mean 425, for example, then =SUMIF(A1:A100,"<>425")
will do it. Is that what you mean?
Click to expand...

Thank you both.
I didn't quite understand Bernie's reply and Bob's did what I wanted
it to, so I went with that.
 
Back
Top