Total of col with N/A's

[Doug Jefferson]

I need to have totals for a number of col's that need to show #n/a and not a 0 or a 0.00

Example:

UC Units Amount
A1 5 10.00
A2 2 2.00
A3 #N/A #N/A
B5 3 15.00

Tot: 10 22.00

Normally I could change the #N/A to an amount via an if statement, but I have to show the #N/A in the detail.

thank you for your help.

 

[Joe4]

Take a look at Aladdin's formulas here:
http://www.mrexcel.com/forum/showpost.php?p=2186879&postcount=2

 

[T. Valko]

I need to have totals for a number of col's that need to show #n/a and not a 0 or a 0.00

Example:

UC Units Amount
A1 5 10.00
A2 2 2.00
A3 #N/A #N/A
B5 3 15.00

Tot: 10 22.00

Normally I could change the #N/A to an amount via an if statement, but I have to show the #N/A in the detail.

thank you for your help.

Try this...

Book1
	A	B	C
1	UC	Units	Amount
2	A1	5	10
3	A2	2	2
4	A3	#N/A	#N/A
5	B5	3	15
6	Totals	10	27
Sheet1

Formula entered in B6 and copied across:

=SUMIF(B2:B5,"<1E100")

 

[Doug Jefferson]

I just attempted both formulas and the 1st one returned 0 back as the result. The 2nd formula returns an error, It looks like part of the logic is missing since it has an uneven number of ( and ) in the formula.

I am not sure what the Control + Shift+Enter comment is for in the post?

Thank you.

 

[Joe4]

I tried Aladdin's first formula, and it worked perfectly for me. Can you post your adaption of the formula?

In the second one, Control + Shift + Enter means that when you enter (type) the formula in, when you are finished you must hit "Control + Shift + Enter" (all three keys) at the same time instead of just entering the formula with the "Enter" key.

 

[T. Valko]

I just attempted both formulas and the 1st one returned 0 back as the result. The 2nd formula returns an error, It looks like part of the logic is missing since it has an uneven number of ( and ) in the formula.

I am not sure what the Control + Shift+Enter comment is for in the post?

Thank you.

You don't need all that over-complication.

See my other reply.

 

[Doug Jefferson]

I just tried the =sumif(B1: B3,"<1E100") and it returned the correct amount. I am not sure what the "<1E100" part of the formula does?

Thank everyone for their assistance on this.

 

[Doug Jefferson]

Joe,

I understand what you mean by hitting all 3 keys at the same time but I am not familiar with how this ties into a formula; I assume it makes Excel treat it differently?

 

[Joe4]

Yes, it treats it as an array formula: http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

 

[T. Valko]

I just tried the =sumif(B1: B3,"<1E100") and it returned the correct amount. I am not sure what the "<1E100" part of the formula does?

Thank everyone for their assistance on this.

1E100 is scientific notation, a "shorthand" method for writing very large numbers.

1E100 represents the number 1 followed by 100 zeros.

In plain English the formula is "saying":

Sum the numbers in the range if they are less than the very large number 1 followed by 100 zeros.

There is a 100% chance that the numbers in the range will be less than 1 followed by 100 zeros so we get the sum result we expect. In this application the #N/A errors are ignored.