If statement with a range selection

[reaktorblue]

Hello everyone, this is my first time posting here. Anyway I was making a simple spreadsheet as I'm taking practice tests for some IT certifications and the spreadsheet I was making was to chart out my progress.

I had a range of scores in some cells and one of the formulas I used so that I didnt have errors in the sheet like div/0 went like the following:

=if(b5:b50="","",sum(b5:b50)) this seemed to work really well for the one section, however I tried to get a little fancy as always and I think I messed something up. Beneath that I tried to separate each practice test with the same sort of formula resulting something like the following:

=if(b30:b40="","",sum(b30:b40)) this however just returned an error #value. I dont understand why the first formula worked and the second didnt. Then again, I'm not really an excel guru, back in college I was really good with it but I dont really remember much. If someone could shed some light as to what I might be doing wrong I would greatly appreciate it! Thanks.

 

[Scott Huish]

Try this:

If you only want to sum if all numbers are present:

=IF(COUNT(B5:B50)=ROWS(B5:B50),SUM(B5:B50),"")

although I don't quite see how this solves your #DIV/0 problem.

 

[Lewiy]

Hi and welcome to the board!!

Are you trying to perform a sum of the range only if the entire range is not blank? Where would Div/0 errors come into it? Your formula looks odd because you seem to be evaluating a range with a single test value. If you can explain what you are actually trying to achieve, I'm sure we can give you an answer!

 

[debooo]

isnt it the exact same formula?

are all the cells b30:b40 numbers?

also, does the first formula really work?

if b5 is blank but b6:b50 have text does the fomrula still sum? i would think you would want it to, but i dont think that formula would.

try using this

=IF(SUM(B5:B50)>0,SUM(B5:B50),"")

 

[Boller]

The formula is an array formula.
It needs to entered with Ctrl+Shift+Enter.

An alternative non-array formula :-

=IF(COUNTA(B5:B50)=0,"",SUM(B5:B50))

Another way would be to custom format the cells :

0;-0;;

and then just use =SUM(B5:B50)

 

[reaktorblue]

I basically dont want to see anything unless there are numbers in certain fields, then I'd like to see the solutions.

=IF(B2:B41="","",SUM(B2:B41) - this was my original formula. Basically it adds all of the total questions I have completed on my certification practice tests. If I dont have any number of questions listed in the sheet, it would be blank.

=IF(G2="","",SUM(H2/G2)) - this is my original formula as well. This basically gives me a total score in % so for example if I had 100 total qusetions and 50 of them right, it would give me a 50%. If I dont put anything in the total questions field, the above formula would be blank and if that one is blank, then this one wouldnt have anything in its field either.

The problem is, without these formulas, I would get 0's in some fields because they were technically blank and then the field listed above would read #DIV/0! because its 0/0 and apparently anything divided by 0 in excel results in that error message.

My problem that I am looking for a solution to though lies in I wanted to be able to break up all of my total questions by material, so say for example, b2:b10 is exam 1 and b11:b20 is exam 2, I wanted to do something like =IF(B11:B20="","",SUM(B11:B20) if that makes sense so I can break things down even further for the different exams I'm taking. My problem that I dont understand is that the first formula seems to work for me but when I try to do it by breaking it up by section, it doesnt seem to work, it gives me a #VALUE error.

Any advice?

 

[Lewiy]

Could you not use something like:
=IF(SUM(B2:B41)>0,SUM(B2:B41),"")

 

[Boller]

You say that this formula works :-

=IF(B2:B41="","",SUM(B2:B41))

I think you will find that it will return "" provided B2 is blank, and return the sum of B2:B41 if B2 is not blank.

Is that what you want?
If so, this does the same thing :-

=IF(B2="","",SUM(B2:B41))

The problem is, without these formulas, I would get 0's in some fields because they were technically blank and then the field listed above would read #DIV/0! because its 0/0 and apparently anything divided by 0 in excel results in that error message.

If you divide a number by a blank cell instead of dividing by 0, you'll get a #VALUE! error instead of #DIV!

There are a number of ways of avoiding errors being returned.
The simplest (usually not the most efficient) is :-

=IF(ISERROR(your formula),"",your formula)

 

[Scott Huish]

#DIV/0! won't be caused by SUM unless there is a #DIV/0! error in the range you are summing. It is still not clear what you are trying to do. But you can't compare a range to a single value in that manner without an array formula, or other method as has been suggested. Perhaps you could provide a sample of your data and expected results.

 

[reaktorblue]

You say that this formula works :-

=IF(B2:B41="","",SUM(B2:B41))

I think you will find that it will return "" provided B2 is blank, and return the sum of B2:B41 if B2 is not blank.

This is close to what I am looking for the formula to do. Basically what I want it to do is when the cells from B2 to B41 are blank, then return "", if any one of the cells from that range (B2, B11, etc to B41) have any data in them, then return the sum of B2:B41

Does this help explain what I am trying to accomplish?