I've got a number of fields with VLOOKUP formula that will NOT be populated
until a later date. However, I'm trying to sum up all fields at the same
time, even though they won't be populated.

As you would expect, I'm getting a "#NA" for fields that are not populated
and the SUM is also "#NA".

Is there a way to bypass all "#NA" fields when I SUM them and get the total
of all fields that ARE populated?

Thanks in advance for your help.

G

One way:

=sum(if(isnumber(a1:a10),a1:a10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column--oddly enough, you
can use the whole row.

or maybe...

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



G wrote:

I've got a number of fields with VLOOKUP formula that will NOT be populated
until a later date. However, I'm trying to sum up all fields at the same
time, even though they won't be populated.

As you would expect, I'm getting a "#NA" for fields that are not populated
and the SUM is also "#NA".

Is there a way to bypass all "#NA" fields when I SUM them and get the total
of all fields that ARE populated?

Thanks in advance for your help.

G

--

Dave Peterson

"Dave Peterson" wrote in message
...
One way:

=sum(if(isnumber(a1:a10),a1:a10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

Adjust the range to match--but you can't use the whole column--oddly
enough, you
can use the whole row.

Not really odd, array formulae have a limit of 65535, a row can only get to
256.

I guess it depends on what odd is?

I find it odd that 65535 elements are ok, but 65536 ain't.

===
And it's nice to note that these limits have been changed in the upcoming
version of excel. (Whole columns will be ok.)


Bob Phillips wrote:

"Dave Peterson" wrote in message
...
One way:

=sum(if(isnumber(a1:a10),a1:a10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

Adjust the range to match--but you can't use the whole column--oddly
enough, you
can use the whole row.

Not really odd, array formulae have a limit of 65535, a row can only get to
256.

--

Dave Peterson

2^16-1

2x8 bit bytes, maximum value

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
I guess it depends on what odd is?

I find it odd that 65535 elements are ok, but 65536 ain't.

===
And it's nice to note that these limits have been changed in the upcoming
version of excel. (Whole columns will be ok.)


Bob Phillips wrote:

"Dave Peterson" wrote in message
...
One way:

=sum(if(isnumber(a1:a10),a1:a10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

Adjust the range to match--but you can't use the whole column--oddly
enough, you
can use the whole row.

Not really odd, array formulae have a limit of 65535, a row can only get
to
256.

--

Dave Peterson

256^2-1

16^4-1

spooky

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
2^16-1

2x8 bit bytes, maximum value

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
I guess it depends on what odd is?

I find it odd that 65535 elements are ok, but 65536 ain't.

===
And it's nice to note that these limits have been changed in the
upcoming
version of excel. (Whole columns will be ok.)


Bob Phillips wrote:

"Dave Peterson" wrote in message
...
One way:

=sum(if(isnumber(a1:a10),a1:a10))

This is an array formula. Hit ctrl-shift-enter instead of enter.
If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

Adjust the range to match--but you can't use the whole column--oddly
enough, you
can use the whole row.

Not really odd, array formulae have a limit of 65535, a row can only
get
to
256.

--

Dave Peterson

I understand the arithmetic. I just find it odd.

Bob Phillips wrote:

2^16-1

2x8 bit bytes, maximum value

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
I guess it depends on what odd is?

I find it odd that 65535 elements are ok, but 65536 ain't.

===
And it's nice to note that these limits have been changed in the upcoming
version of excel. (Whole columns will be ok.)


Bob Phillips wrote:

"Dave Peterson" wrote in message
...
One way:

=sum(if(isnumber(a1:a10),a1:a10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

Adjust the range to match--but you can't use the whole column--oddly
enough, you
can use the whole row.

Not really odd, array formulae have a limit of 65535, a row can only get
to
256.

--

Dave Peterson

--

Dave Peterson

You are best off to avoid the #na's coming out of the vlookups. My prefered
method is to use a countif something like this...

=if(countif(B1:B10, A1) = 0, 0, vlookup(A1, B1:C10, 2, false))
--
HTH...

Jim Thomlinson


"G" wrote:

I've got a number of fields with VLOOKUP formula that will NOT be populated
until a later date. However, I'm trying to sum up all fields at the same
time, even though they won't be populated.

As you would expect, I'm getting a "#NA" for fields that are not populated
and the SUM is also "#NA".

Is there a way to bypass all "#NA" fields when I SUM them and get the total
of all fields that ARE populated?

Thanks in advance for your help.

G