Calculations with NA fields

[wafs]

I need to calculate a column, but some of the column will have NA in them. I know that if I use the SUM in excel, my final answer would be NA. Is there anyway in VBA that I could get around it?

I was looking at something like:

If range("A5:A50") <> "" then
range("A5:A50")=sum

Still working on the correct verbage for the code.

 

[xlbo]

do you really want to do this in code ??
can be done with a standard formula......

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[wafs]

I tried using an if statement, right in excel, and it doesn't like my if statement to many calculations. The regular sum, in excel, show the total as NA.

 

[macropod]

Hi wafs,

As per my reply in the Office forum, try:
=SUM(IF(ISERROR(A1:A100),,A1:A100))
as an array formula (ie input with ctrl-shift-enter), where A1:A100 is the range you want to sum.

Cheers
PS: You could also use:
=SUM(IF(ISNA(A1:A100),,A1:A100))
but the version above also handles other error forms, such as #DIV/0!, #VALUE!, etc.

 

[AnotherHiggins]

FYI to all reading this: This thread is related to thread68-1211035 in the Microsoft: Office Forum.

wafs,

You could also use my suggestion from that other thread (which isn't an array formula, just enter it normally):
=SumIf(D,"<>#N/A")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[wafs]

I ended up using =subtotal(109,A1:A5) and it works.

 

[xlbo]

Not quite sure how that works unless it has changed post XP but in my help file, there are only options 1 through 11...if I try 109, I get a #VALUE! error....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Geoff,
From Excel 2003 help:
Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.
101 to 111 only works with hidden rows, not hidden columns.

Regards,


Gavin

 

[xlbo]

aha - fair enough - changed quite a bit from XP then ;-)

Thanks Gavin

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question