0/0 = #VALUE how can I ignore it?

[mattysmithuk11]

Hi im basically having a problem in a spreadsheet where a number of percentages are added up to give a number however not all of the possible cells where the percentages reside are populated for example there are 10 cells which are added however in some cases only 5 of them contain a percentage the others contain nothing and are showing #VALUE this I can understand this as the cells being used to calculate the percentage are empty or populated with 0's hense 0/0= impossible but how can I get my addition formula to ignore cells where there is a #VALUE and only add the cells with numbers in.

Here is my current formula for the addition of the cells.
=V12+S12+Q12+O12+M12+H12+F12+D12

if any of the cells added have #VALUE in (which they will if there is no data) then the resultant is #VALUE

Thanks in advance I did do a search but couldnt find anything that worked.

[TheGurkha]

Hi and welcome to the forums.

Try this: =SUMIF(A1:A10;">0";A1:A10)

[mattysmithuk11]

Hi and welcome to the forums.

Try this: =SUMIF(A1:A10;">0";A1:A10)

Hi ive tried this in a new sheet and it seems to work the issue ive got is that the cells arnt sequentional and from that formula it would add cells in the range A1 through to A10 how can I get it to just look and add from cells A1 A3 A5 e.t.c. thanks for the help.
This is what im trying now,
=SUMIF( V2,S2,Q2,O2,M2,H2,F2,D2;">0";V2,S2,Q2,O2,M2,H2,F2,D2)

The resultant of that is #Name?

[TheGurkha]

Hi and welcome to the forums.

Try this: =SUMIF(A1:A10;">0";A1:A10)

Hi ive tried this in a new sheet and it seems to work the issue ive got is that the cells arnt sequentional and from that formula it would add cells in the range A1 through to A10 how can I get it to just look and add from cells A1 A3 A5 e.t.c. thanks for the help

Yes, it was an example of the concept you need to use, not a cell for cell drop in solution for your case.

You need to do something like =IF(ISNUMBER(A1);A1;0)+IF(ISNUMBER(A2);A2;0)+ ... etc. But this is long winded and ugly.

Another way to do it is to insert a new row at row 13 and copy the values of those cells you want to sum into it, hide the row, and sum those cells with the format of the simpler Sumif() formula I gave before.

[Cambirder]

I can understand this as the cells being used to calculate the percentage are empty or populated with 0's hense 0/0= impossible but how can I get my addition formula to ignore cells where there is a #VALUE and only add the cells with numbers in.

Why not treat the issue by getting rid of the problem values at the point they are generated.

E.G. If Cell C1 has the formula =A1/B1 then a value of 0 in B1 will give you a problem. If you change C1 to =IF(B1=0;"";A1/B1) then C1 will give you a blank cell if B1 = 0

[TheGurkha]

Ah, good thinking.