Subtotal not working with #N/A in the cell
 

I have a hllookup formula that provides certain results as #N/A....I
understand why I get a #N/A, however, I need to subtotal the row. I tried to
use the subtotal formula, but it does not recognize #N/A as a number,
obviously cause it is not a number. I need to add these up for example:
10, 20, #N/A, #N/A, Subtotal: 30

How can I convert the #N/A so that they are "0" or blank so that my formula
works?

Thanks!

Subtotal not working with #N/A in the cell
 

=if(iserror(hlookup(...)),0,hlookup(...))
=if(iserror(hlookup(...)),"",hlookup(...))

to return 0 or to return an empty string (cell looks empty).

Send Object Command - Two attachments wrote:

I have a hllookup formula that provides certain results as #N/A....I
understand why I get a #N/A, however, I need to subtotal the row. I tried to
use the subtotal formula, but it does not recognize #N/A as a number,
obviously cause it is not a number. I need to add these up for example:
10, 20, #N/A, #N/A, Subtotal: 30

How can I convert the #N/A so that they are "0" or blank so that my formula
works?

Thanks!

--

Dave Peterson

Subtotal not working with #N/A in the cell
 

In the originating HLOOKUP use an IF(ISNA...combination

=IF(ISNA(YourHLOOKUP),0,YourHLOOKUP))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Send Object Command - Two attachments"
soft.com wrote in message
...
I have a hllookup formula that provides certain results as #N/A....I
understand why I get a #N/A, however, I need to subtotal the row. I tried
to
use the subtotal formula, but it does not recognize #N/A as a number,
obviously cause it is not a number. I need to add these up for example:
10, 20, #N/A, #N/A, Subtotal: 30

How can I convert the #N/A so that they are "0" or blank so that my
formula
works?

Thanks!

Subtotal not working with #N/A in the cell
 

Correction

IF(ISNA(YourHLOOKUP),0,YourHLOOKUP)


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Nick Hodge" wrote in message
...
In the originating HLOOKUP use an IF(ISNA...combination

=IF(ISNA(YourHLOOKUP),0,YourHLOOKUP))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Send Object Command - Two attachments"
soft.com wrote in
message ...
I have a hllookup formula that provides certain results as #N/A....I
understand why I get a #N/A, however, I need to subtotal the row. I
tried to
use the subtotal formula, but it does not recognize #N/A as a number,
obviously cause it is not a number. I need to add these up for example:
10, 20, #N/A, #N/A, Subtotal: 30

How can I convert the #N/A so that they are "0" or blank so that my
formula
works?

Thanks!

Subtotal not working with #N/A in the cell
 

Works great! Thank You.

"Nick Hodge" wrote:

Correction

IF(ISNA(YourHLOOKUP),0,YourHLOOKUP)


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Nick Hodge" wrote in message
...
In the originating HLOOKUP use an IF(ISNA...combination

=IF(ISNA(YourHLOOKUP),0,YourHLOOKUP))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Send Object Command - Two attachments"
soft.com wrote in
message ...
I have a hllookup formula that provides certain results as #N/A....I
understand why I get a #N/A, however, I need to subtotal the row. I
tried to
use the subtotal formula, but it does not recognize #N/A as a number,
obviously cause it is not a number. I need to add these up for example:
10, 20, #N/A, #N/A, Subtotal: 30

How can I convert the #N/A so that they are "0" or blank so that my
formula
works?

Thanks!

Subtotal not working with #N/A in the cell
 

Great

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Send Object Command - Two attachments"
soft.com wrote in message
...
Works great! Thank You.

"Nick Hodge" wrote:

Correction

IF(ISNA(YourHLOOKUP),0,YourHLOOKUP)


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Nick Hodge" wrote in message
...
In the originating HLOOKUP use an IF(ISNA...combination

=IF(ISNA(YourHLOOKUP),0,YourHLOOKUP))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Send Object Command - Two attachments"
soft.com wrote in
message ...
I have a hllookup formula that provides certain results as #N/A....I
understand why I get a #N/A, however, I need to subtotal the row. I
tried to
use the subtotal formula, but it does not recognize #N/A as a number,
obviously cause it is not a number. I need to add these up for
example:
10, 20, #N/A, #N/A, Subtotal: 30

How can I convert the #N/A so that they are "0" or blank so that my
formula
works?

Thanks!