#VALUE! Error

[Hedy]

I am totaling rows of data and am getting the value error on some of the rows #VALUE! (see example below)

I would like to have this formula ignore cells when there are formulas but no values, for example C7, getting rid of the value error.

=SUM(C$2*C7,D$2*D7,E$2*E7,F$2*F7,G$2*G7,H$2*H7,I$2*I7,J$2*J7,K$2*K7,L$2*L7,M$2*M7,N$2*N7)

[AlKey]

Maybe using IFERROR function

=IFERROR(SUM(C$2*C7,D$2*D7,E$2*E7,F$2*F7,G$2*G7,H$2*H7,I$2*I7,J$2*J7,K$2*K7,L$2*L7,M$2*M7,N$2*N7),"")

[Hedy]

Thank you for this info. However, I used your formula, and am not getting a sum for the other values. I need a sum for the cells that do have values in them.

[sktneer]

May be you should try this.....

Please Login or Register  to view this content.

Does this help?

[AlKey]

The IFEEROR is to hide errors only and it does not effect calculations.

Maybe you can upload a spreadsheet with some examples where it shows errors.

[Hedy]

Attached is a sample spreadsheet - thanks!

[Tony Valko]

Have no idea what you're wanting to do based on that file.

There are no formulas and the formula you posted in reply #1 doesn't match the data structure.

In column N you have these constant cell values:

Data Range

	N
1
2	TOTAL
3	281
4	313
5	303
6	207
7	#VALUE!
8	#VALUE!
9	#VALUE!
10	277

What do they mean?

[AlKey]

Try this on your test sheet

=SUMPRODUCT(($B$1:$M$1)*(B3:M3&0))/10

[Hedy]

sorry, my attempt at attaching a sample spreadsheet did not work well.

Basically what I am trying to do is include in my calculation cells that may have a formula in them yet show empty. The cells reference another worksheet so the C11s, E11s, etc. may be =DATAINPUT!C$11 or =DATAINPUT!E$11, and the C11 may be empty since there is no value to bring over, and the E11 may have a value of 10. Hopefully this helps to clarify. What seems to be the problem are the C11 type cells with no value.

=SUM(C$2*C11,D$2*D11,E$2*E11,F$2*F11,G$2*G11,H$2*H11,I$2*I11,J$2*J11,K$2*K11,L$2*L11)

[sktneer]

Did you try?
In N3

Please Login or Register  to view this content.

and copy down.