If statement with a range selection

reaktorblue

Board Regular
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.

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

Scott Huish

MrExcel MVP
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

Well-known Member
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

Board Regular
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

Banned
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

Board Regular
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.

Lewiy

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

Boller

Banned
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 :-

Scott Huish

MrExcel MVP
#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

Board Regular
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?

Replies
4
Views
680
Replies
0
Views
595
Replies
4
Views
353
Replies
5
Views
2K
Replies
3
Views
425

1,191,584
Messages
5,987,479
Members
440,097
Latest member
Wint

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back