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!
