Calculating a sum, exceptions for specific values

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?
 
B

Bernie Deitrick

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
 
B

Bernie Deitrick

Duh! (smacking forehead)

And, of course,

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

HTH,
Bernie
MS Excel MVP
 
B

Bob Umlas, Excel MVP

If by specific number yuo mean 425, for example, then =SUMIF(A1:A100,"<>425")
will do it. Is that what you mean?
 
R

RedBeard

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.
 
Top