Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hi I am trying to get a formula to get the sum but if a range is selected its automatically 0
Solved by O. J. in 35 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 04/06/2018 - 10:17
Good Afternoon. So you are having problems with the sum function?
Excelchat Expert 04/06/2018 - 10:18
Can you share what your range looks like and I think we can fix this quickly.
User 04/06/2018 - 10:18
Hi yes i am actually trying to find a best formula so I can a percentage per evaluation but since there are some non negotiable that will lead to a 0 that will make the percent no matter what is selected 0
Excelchat Expert 04/06/2018 - 10:19
You need to use SumIf and Countif functions. Have you used them before?
User 04/06/2018 - 10:19
That is from my document but I have used sumif but I get stuck when I go a little father
Excelchat Expert 04/06/2018 - 10:21
Ok, sumif uses the form =sumif([criteria_range], criteria,[sum_range]) without the brackets of course
Excelchat Expert 04/06/2018 - 10:22
What is the cell in your sheet where you are having the problem?
User 04/06/2018 - 10:22
=Sumif((B24/D24),Countif(B20:B23)=15,0) is the expression im trying to use
Excelchat Expert 04/06/2018 - 10:22
Cell G5?
User 04/06/2018 - 10:22
The range I want to make sure makes it a 0 is from b20 to b23
User 04/06/2018 - 10:23
No matter if they get 1 point on the others
Excelchat Expert 04/06/2018 - 10:23
So the non-negotiables negate the others if they are true?
User 04/06/2018 - 10:23
since its for a quality guide, I want to be sure that 15 points is the max a representative can get, but I would want to make sure that if a non negotiable is met then it will negate the points
User 04/06/2018 - 10:23
exactly!
Excelchat Expert 04/06/2018 - 10:26
OK. I see. So if any cell between b20 and b23 is 15 then the answer is 0 or is it that any cell between b20 and b23 = 0, then the answer is 0?
User 04/06/2018 - 10:27
Any cell between b20 and b23 is 0
User 04/06/2018 - 10:27
the 15 I thought it would be easier to have a formula that would take away the 15 from the total score if a NN was met but it would be easire to leave it 0 and having the score be then 0 at the end
User 04/06/2018 - 10:27
easier*
Excelchat Expert 04/06/2018 - 10:28
OK try this: =if(sum(b20:B23)<60,0,b24/d24)
User 04/06/2018 - 10:30
Thats the little issue, that one would work, but im doing a full sheet formula, so it divides and takes into consideration if a NN was selected that way i still have a percent at the end
User 04/06/2018 - 10:30
its to be used as a total score.
User 04/06/2018 - 10:31
=if((sum(B20:B23)<60,0,B24/D24),(B31/D31)) thats what I tried just now but it doesnt work, i know im wrong but I cant seem the correct formula that puts what I want it to do on paper
Excelchat Expert 04/06/2018 - 10:31
So is the total possible always 15? And you're looking for what % of 15 the score is unless they don't have the non-negotiables, then it is 0%
User 04/06/2018 - 10:32
Exactly
User 04/06/2018 - 10:32
thats right on the dot
Excelchat Expert 04/06/2018 - 10:33
I don't see any values in D31 or B31.
User 04/06/2018 - 10:33
I copied that from my sheet im sorry
Excelchat Expert 04/06/2018 - 10:33
What is in B31?
User 04/06/2018 - 10:34
this is how it looks like on my sheet
User 04/06/2018 - 10:35
B is where the points would go in
User 04/06/2018 - 10:35
Where it says total score that is where I want the final score to go into
Excelchat Expert 04/06/2018 - 10:37
You're very close to the answer. If you are using 15 as the score for a non negotiable if they have it, then =if(sum(b20:b23)<60,0,b31/d31). That will give you a percentage based on them having all the non-negotiables.
Excelchat Expert 04/06/2018 - 10:38
That will give them a total score of 0% if they don't have one of the non-negotiables.
Excelchat Expert 04/06/2018 - 10:39
the if statement is always =if(logical test, true, false). So the logical statement is true, the first response is if it is true, the second if it is false
Excelchat Expert 04/06/2018 - 10:40
Does that make sense to you? Is that what you were looking for?
User 04/06/2018 - 10:41
now I see, so the sum is pretty much the full range and then opting for what they must meet and then dividing at the end to give a final number.
Excelchat Expert 04/06/2018 - 10:41
Yes, exactly.
Excelchat Expert 04/06/2018 - 10:42
What you're saying is that they must meet certain criteria to even have a score to be considered.
Excelchat Expert 04/06/2018 - 10:43
They could have 15 out of 15 but if they lacked one non-negotiable they would be disqualified.
User 04/06/2018 - 10:43
Using this sheet as an example it would stay at 0
Excelchat Expert 04/06/2018 - 10:44
Yes. You would have to fill in 15 for each of the non-negotiables if they have them. Or you could use 1 for each of them and just change the criteria to =if(sum(b20:b23)<4,......
Excelchat Expert 04/06/2018 - 10:45
Wait, do the 15 points include the non-negotiables?
User 04/06/2018 - 10:46
No the 15 is the over all points available to get a 100% the 15 I put since I thought it would be easier to be 15 since its the max amount of points possible per evaluation and if one was selected it would make the value of the score 0
Excelchat Expert 04/06/2018 - 10:48
Then your sum in B31 needs to be the sum of b8:B19()
Excelchat Expert 04/06/2018 - 10:48
forget the parentheses
User 04/06/2018 - 10:49
So that would count only that range but I need it to go all the way down to where the NN are]
User 04/06/2018 - 10:49
wouldnt it go from B8 to B30?
Excelchat Expert 04/06/2018 - 10:49
Only down to the non-negotiables. They only are for qualification and really not part of the score
User 04/06/2018 - 10:50
Thats right
User 04/06/2018 - 10:50
So it would stop prior
Excelchat Expert 04/06/2018 - 10:50
Yes
Excelchat Expert 04/06/2018 - 10:50
Now, does it make sense?
User 04/06/2018 - 10:50
Now it does, thank you!!
Excelchat Expert 04/06/2018 - 10:51
Just make sure the sum of the non-negotiables = 15 or whatever your criteria is.
Excelchat Expert 04/06/2018 - 10:51
I'm glad I could help. Thanks for using Got It Pro.
User 04/06/2018 - 10:52
Thank you for the help!
Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc