ExcelBanter - SUM Issue.. HELP

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - SUM Issue.. HELP (https://www.excelbanter.com/excel-discussion-misc-queries/166203-sum-issue-help.html)

Setting a Mileage Expense Report[_2_]

SUM Issue.. HELP

Need to get a total of a column but the column contains N/A until information
it typed then it gives you a number.. i need the numbers to add... ???

Example below

3 $1.46
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A

need totals to say 3 and $1.46 ... NOT
#VALUE!
#N/A

Elkar

SUM Issue.. HELP

One way:

=SUMIF(A1:A10,"<#N/A")

But you may want to consider modifying the formulas that are causing the
#N/A errors so that they won't appear in the first place. Something like:

=IF(ISNA(YourFormula),"",YourFormula)

or maybe:

=IF(ISNA(YourFormula),"No Data",YourFormula)

Thus, if your formula results in #N/A, a blank "" or "No Data" would be
returned instead. Either of these options would not interfere with the SUM
function in your totals.

HTH,
Elkar

"Setting a Mileage Expense Report" wrote:

Need to get a total of a column but the column contains N/A until information
it typed then it gives you a number.. i need the numbers to add... ???

Example below

3 $1.46
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A

need totals to say 3 and $1.46 ... NOT
#VALUE!
#N/A

Peo Sjoblom

SUM Issue.. HELP

This variety ignores all type of errors

=SUMIF(A1:A10,"<0"&999^99)

--

Regards,

Peo Sjoblom

"Elkar" wrote in message
...
One way:

=SUMIF(A1:A10,"<#N/A")

But you may want to consider modifying the formulas that are causing the
#N/A errors so that they won't appear in the first place. Something like:

=IF(ISNA(YourFormula),"",YourFormula)

or maybe:

=IF(ISNA(YourFormula),"No Data",YourFormula)

Thus, if your formula results in #N/A, a blank "" or "No Data" would be
returned instead. Either of these options would not interfere with the
SUM
function in your totals.

HTH,
Elkar

"Setting a Mileage Expense Report" wrote:

Need to get a total of a column but the column contains N/A until
information
it typed then it gives you a number.. i need the numbers to add... ???

Example below

3 $1.46
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A

need totals to say 3 and $1.46 ... NOT
#VALUE!
#N/A

Setting a Mileage Expense Report[_2_]

SUM Issue.. HELP

Thank YOU!!

"Elkar" wrote:

One way:

=SUMIF(A1:A10,"<#N/A")

But you may want to consider modifying the formulas that are causing the
#N/A errors so that they won't appear in the first place. Something like:

=IF(ISNA(YourFormula),"",YourFormula)

or maybe:

=IF(ISNA(YourFormula),"No Data",YourFormula)

Thus, if your formula results in #N/A, a blank "" or "No Data" would be
returned instead. Either of these options would not interfere with the SUM
function in your totals.

HTH,
Elkar

"Setting a Mileage Expense Report" wrote:

Need to get a total of a column but the column contains N/A until information
it typed then it gives you a number.. i need the numbers to add... ???

Example below

3 $1.46
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A

need totals to say 3 and $1.46 ... NOT
#VALUE!
#N/A

Setting a Mileage Expense Report[_2_]

SUM Issue.. HELP

Thank you

"Peo Sjoblom" wrote:

This variety ignores all type of errors

=SUMIF(A1:A10,"<0"&999^99)

--

Regards,

Peo Sjoblom

"Elkar" wrote in message
...
One way:

=SUMIF(A1:A10,"<#N/A")

But you may want to consider modifying the formulas that are causing the
#N/A errors so that they won't appear in the first place. Something like:

=IF(ISNA(YourFormula),"",YourFormula)

or maybe:

=IF(ISNA(YourFormula),"No Data",YourFormula)

Thus, if your formula results in #N/A, a blank "" or "No Data" would be
returned instead. Either of these options would not interfere with the
SUM
function in your totals.

HTH,
Elkar

"Setting a Mileage Expense Report" wrote:

Need to get a total of a column but the column contains N/A until
information
it typed then it gives you a number.. i need the numbers to add... ???

Example below

3 $1.46
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A

need totals to say 3 and $1.46 ... NOT
#VALUE!
#N/A

All times are GMT +1. The time now is 12:43 PM.