Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value
at some where other than zero?

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then you'd
get the true answer. You can substitute a value for "invalid" such as zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value
at some where other than zero?

Got it, thanks. I just had to use a cell reference with the value I wanted
so the graph would show it crossing at the average I have set it at.

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then you'd
get the true answer. You can substitute a value for "invalid" such as zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value
at some where other than zero?

Glad to have helped some, and thanks for the feedback.

"SkiRJB" wrote:

Got it, thanks. I just had to use a cell reference with the value I wanted
so the graph would show it crossing at the average I have set it at.

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then you'd
get the true answer. You can substitute a value for "invalid" such as zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value
at some where other than zero?

Helped me too. Thanks.

I used the IF Statement to get rid of the Error and substituted a period.

Saved me a lot of time and headache. Literally.

I have a spreadsheet that i have entered a really long formula into. The
data I have now, in some parts, is empty (since I wanted to go ahead and set
up the formulas for future ease). However, since the fields are empty (I've
put dashes (-) in as place holders), I get the #VALUE! error. Is there
someway that I can do an If statement so that If I get the #VALUE! error, the
cell that contains the formula (and the error) will come up empty?

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then you'd
get the true answer. You can substitute a value for "invalid" such as zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value
at some where other than zero?

Do not place dashes in the cells. These dashes change the cell from blank to
non-blank.

Keep them empty/blank then you won't have to be concerned with them.

Why do you need place-holders?

What do they do?

A possible error trap would be =IF(cellref="-","",else whatever)


Gord Dibben MS Excel MVP

On Thu, 26 Jun 2008 14:08:03 -0700, Amy wrote:

I have a spreadsheet that i have entered a really long formula into. The
data I have now, in some parts, is empty (since I wanted to go ahead and set
up the formulas for future ease). However, since the fields are empty (I've
put dashes (-) in as place holders), I get the #VALUE! error. Is there
someway that I can do an If statement so that If I get the #VALUE! error, the
cell that contains the formula (and the error) will come up empty?

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then you'd
get the true answer. You can substitute a value for "invalid" such as zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value
at some where other than zero?

I was using place holders because the spreadsheet is being used for external
reporting and I wanted to make it clear that they were intentionally blank.
When I left the cells completely empty, I was getting the #DIV/0 error for
having the formula refer to empty cells. So, the same initial question would
still apply since I would like to eliminate the unattractive error message
for the external reporting but I want to leave the formula there since it
will be used inn future terms.

Thanks

"Gord Dibben" wrote:

Do not place dashes in the cells. These dashes change the cell from blank to
non-blank.

Keep them empty/blank then you won't have to be concerned with them.

Why do you need place-holders?

What do they do?

A possible error trap would be =IF(cellref="-","",else whatever)


Gord Dibben MS Excel MVP

On Thu, 26 Jun 2008 14:08:03 -0700, Amy wrote:

I have a spreadsheet that i have entered a really long formula into. The
data I have now, in some parts, is empty (since I wanted to go ahead and set
up the formulas for future ease). However, since the fields are empty (I've
put dashes (-) in as place holders), I get the #VALUE! error. Is there
someway that I can do an If statement so that If I get the #VALUE! error, the
cell that contains the formula (and the error) will come up empty?

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then you'd
get the true answer. You can substitute a value for "invalid" such as zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value
at some where other than zero?

please also help me: i use this formula:

=SUM(IF(ISERROR(H449:H508),0,H449:H508))

and i believe that i have it right but it would just display a zero value
("0"). i tried to evaluate the function it would give me the right answer
but just don't display in the worksheet.

1 LFKNA Cosmetics - Liquid Foundation - Nude Amber 195.00 195.00
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A

GROSS AMOUNT Php- <<< not displaying the answer which is supposed to
be "195.00"


"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then you'd
get the true answer. You can substitute a value for "invalid" such as zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value
at some where other than zero?

=SUM(IF(ISERROR(H449:H508),0,H449:H508))

That is an array formula and needs to be entered using the key combination
of CTRL, SHIFT, ENTER, not just ENTER.

Try this non-array alternative (normally entered):

=SUMIF(H449:H508,"<1E100")


--
Biff
Microsoft Excel MVP


"stylissimo" wrote in message
...
please also help me: i use this formula:

=SUM(IF(ISERROR(H449:H508),0,H449:H508))

and i believe that i have it right but it would just display a zero value
("0"). i tried to evaluate the function it would give me the right answer
but just don't display in the worksheet.

1 LFKNA Cosmetics - Liquid Foundation - Nude Amber 195.00 195.00
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A

GROSS AMOUNT Php- <<< not displaying the answer which is supposed to
be "195.00"


"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then
you'd
get the true answer. You can substitute a value for "invalid" such as
zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such
as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a
value
at some where other than zero?

I understand how the ISERROR() function handles division by zero in your
example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the
numeral 0. However, the problem I have encountered is that numbers are
downloaded as text with three trailing spaces. I have to manually remove
these trailing spaces to use these numbers in the calculation. This is a
time consuming task.

Is there a better way to automatically convert numbers stored as text to
numeric format? I suppose I could write a macro to automatically remove
three characters from the end of each item in a one-dimensional array (in my
case), but I've never written a macro in Excel 2007. Can the VALUE function
not be used to convert text to numbers? I have had no luck using it to do so.

Any help would be greatly appreciated.

Best wishes,

Lewis Williams

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then you'd
get the true answer. You can substitute a value for "invalid" such as zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value
at some where other than zero?

It's not a good way of handling DIV errors because it will hide other errors

It's better to use

=IF(B1=0,0,A1/B1)


You can install this macro, select the import and run the macro

--


Regards,


Peo Sjoblom

"LHW" wrote in message
...

I understand how the ISERROR() function handles division by zero in your
example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the
numeral 0. However, the problem I have encountered is that numbers are
downloaded as text with three trailing spaces. I have to manually remove
these trailing spaces to use these numbers in the calculation. This is a
time consuming task.

Is there a better way to automatically convert numbers stored as text to
numeric format? I suppose I could write a macro to automatically remove
three characters from the end of each item in a one-dimensional array (in
my
case), but I've never written a macro in Excel 2007. Can the VALUE
function
not be used to convert text to numbers? I have had no luck using it to do
so.

Any help would be greatly appreciated.

Best wishes,

Lewis Williams

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then
you'd
get the true answer. You can substitute a value for "invalid" such as
zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such
as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a
value
at some where other than zero?

You can install this macro, select the import and run the macro

You forgot the macro!

Did you mean this one?

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
It's not a good way of handling DIV errors because it will hide other
errors

It's better to use

=IF(B1=0,0,A1/B1)


You can install this macro, select the import and run the macro

--


Regards,


Peo Sjoblom

"LHW" wrote in message
...

I understand how the ISERROR() function handles division by zero in your
example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the
numeral 0. However, the problem I have encountered is that numbers are
downloaded as text with three trailing spaces. I have to manually remove
these trailing spaces to use these numbers in the calculation. This is a
time consuming task.

Is there a better way to automatically convert numbers stored as text to
numeric format? I suppose I could write a macro to automatically remove
three characters from the end of each item in a one-dimensional array (in
my
case), but I've never written a macro in Excel 2007. Can the VALUE
function
not be used to convert text to numbers? I have had no luck using it to
do so.

Any help would be greatly appreciated.

Best wishes,

Lewis Williams

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then
you'd
get the true answer. You can substitute a value for "invalid" such as
zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such
as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a
value
at some where other than zero?

Thanks.

I seemed to have opened to reply to group windows and I pasted the link into
the one that I closed <g

--


Regards,


Peo Sjoblom

"T. Valko" wrote in message
...
You can install this macro, select the import and run the macro

You forgot the macro!

Did you mean this one?

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
It's not a good way of handling DIV errors because it will hide other
errors

It's better to use

=IF(B1=0,0,A1/B1)


You can install this macro, select the import and run the macro

--


Regards,


Peo Sjoblom

"LHW" wrote in message
...

I understand how the ISERROR() function handles division by zero in your
example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to
the
numeral 0. However, the problem I have encountered is that numbers are
downloaded as text with three trailing spaces. I have to manually
remove
these trailing spaces to use these numbers in the calculation. This is
a
time consuming task.

Is there a better way to automatically convert numbers stored as text to
numeric format? I suppose I could write a macro to automatically remove
three characters from the end of each item in a one-dimensional array
(in my
case), but I've never written a macro in Excel 2007. Can the VALUE
function
not be used to convert text to numbers? I have had no luck using it to
do so.

Any help would be greatly appreciated.

Best wishes,

Lewis Williams

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then
you'd
get the true answer. You can substitute a value for "invalid" such as
zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such
as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a
value
at some where other than zero?

"T. Valko" wrote:

You can install this macro, select the import and run the macro

You forgot the macro!

Did you mean this one?

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
It's not a good way of handling DIV errors because it will hide other
errors

It's better to use

=IF(B1=0,0,A1/B1)


You can install this macro, select the import and run the macro

--


Regards,


Peo Sjoblom

"LHW" wrote in message
...

I understand how the ISERROR() function handles division by zero in your
example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the
numeral 0. However, the problem I have encountered is that numbers are
downloaded as text with three trailing spaces. I have to manually remove
these trailing spaces to use these numbers in the calculation. This is a
time consuming task.

Is there a better way to automatically convert numbers stored as text to
numeric format? I suppose I could write a macro to automatically remove
three characters from the end of each item in a one-dimensional array (in
my
case), but I've never written a macro in Excel 2007. Can the VALUE
function
not be used to convert text to numbers? I have had no luck using it to
do so.

Any help would be greatly appreciated.

Best wishes,

Lewis Williams

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then
you'd
get the true answer. You can substitute a value for "invalid" such as
zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such
as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a
value
at some where other than zero?

Thanks to both Peo Sjoblom and Biff ,

I finally figured out how to run the macro, and it works just fine.

Best wishes,

Lewis Williams



"T. Valko" wrote:

You can install this macro, select the import and run the macro

You forgot the macro!

Did you mean this one?

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
It's not a good way of handling DIV errors because it will hide other
errors

It's better to use

=IF(B1=0,0,A1/B1)


You can install this macro, select the import and run the macro

--


Regards,


Peo Sjoblom

"LHW" wrote in message
...

I understand how the ISERROR() function handles division by zero in your
example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the
numeral 0. However, the problem I have encountered is that numbers are
downloaded as text with three trailing spaces. I have to manually remove
these trailing spaces to use these numbers in the calculation. This is a
time consuming task.

Is there a better way to automatically convert numbers stored as text to
numeric format? I suppose I could write a macro to automatically remove
three characters from the end of each item in a one-dimensional array (in
my
case), but I've never written a macro in Excel 2007. Can the VALUE
function
not be used to convert text to numbers? I have had no luck using it to
do so.

Any help would be greatly appreciated.

Best wishes,

Lewis Williams

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then
you'd
get the true answer. You can substitute a value for "invalid" such as
zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options such
as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a
value
at some where other than zero?

Why does this formula return a #value =SUM(IF(A3:A456=" S Texas",C3:C456))
The values in the a column are Words and c is amounts.

This formula works on a different location
{=SUM(IF(A3:A456="Houston",C3:C456))

Have you confirmed the formula with CTRL+SHIFT+ENTER not just ENTER so
that { } brackets auto appear around the formula?

Also, you can use this instead:

=SUMIF(A3:A456," S Texas",C3:C456)

and only use ENTER to confirm as normal.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126531

Hello,

I am sort of new to Excel 2007.

I am having trouble trying to get the "total" of multiplying currency with
quantity. I don't really understand the help box or the answers on the
message boards.

What I need is someone to show me an example, that way I can get this
taken care of.

In disparate need of help.
Rob

"NBVC" wrote:


Have you confirmed the formula with CTRL+SHIFT+ENTER not just ENTER so
that { } brackets auto appear around the formula?

Also, you can use this instead:

=SUMIF(A3:A456," S Texas",C3:C456)

and only use ENTER to confirm as normal.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126531

Post some samples of the data you're working with and show before and after
examples.


Gord Dibben MS Excel MVP

On Sat, 22 Aug 2009 19:25:01 -0700, Frazzled Rob <Frazzled
wrote:

Hello,

I am sort of new to Excel 2007.

I am having trouble trying to get the "total" of multiplying currency with
quantity. I don't really understand the help box or the answers on the
message boards.

What I need is someone to show me an example, that way I can get this
taken care of.

In disparate need of help.
Rob

"NBVC" wrote:


Have you confirmed the formula with CTRL+SHIFT+ENTER not just ENTER so
that { } brackets auto appear around the formula?

Also, you can use this instead:

=SUMIF(A3:A456," S Texas",C3:C456)

and only use ENTER to confirm as normal.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126531