Calculating a sum, exceptions for specific values

's Computer Specifications

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?

's Computer Specifications

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

"RedBeard" <(E-Mail Removed)> wrote in message
news:372ae717-8e2f-47bd-a35d-(E-Mail Removed)...
> 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?

's Computer Specifications

Duh! (smacking forehead)

And, of course,

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

HTH,
Bernie
MS Excel MVP

"RedBeard" <(E-Mail Removed)> wrote in message
news:372ae717-8e2f-47bd-a35d-(E-Mail Removed)...
> 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?

's Computer Specifications

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

"RedBeard" wrote:

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

's Computer Specifications

On Nov 18, 5:26*pm, Bob Umlas, Excel MVP
<BobUmlasExcel...@discussions.microsoft.com> wrote:
> If by specific number yuo mean 425, for example, then =SUMIF(A1:A100,"<>425")
> will do it. Is that what you mean?
>
> "RedBeard" wrote:
> > 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?
> > .

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.

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST?	yadang	Microsoft Excel Misc	2	1st Oct 2009 06:18 PM
calculating sum and average values for specific days only	Soccerboy83	Microsoft Excel Misc	2	2nd Sep 2009 10:38 PM
calculating Average Value with exceptions	kapiszon	Microsoft Access VBA Modules	8	27th Jun 2008 12:53 PM
CALCULATING A FORMULA TO USE A SPECIFIC CELL'S VALUES	=?Utf-8?B?UGF1bEgtT3o=?=	Microsoft Excel Worksheet Functions	0	26th Mar 2006 11:50 PM
Blocking Specific IP addresses with XP Firewall exceptions	Mouse	Windows XP Security	1	4th Feb 2006 03:43 AM