Setting A Rule So The Total Of Sum Must Be 0

[HurstUOB]

Hello
In the attached file I have a total of 40 (highlighted in yellow) which reduces every time that I enter a value into the B Column. Is there anyway of setting up an error message to appear if after the column is filled the 'Points Remaining' figure displays anything but 0? Basically to ensure that all the points are allocated.

[GeneralDisarray]

Maybe just a conditional formatting message?

See attached.

[HurstUOB]

Maybe just a conditional formatting message?

See attached.

Sorry John the attachment doesn't seem to work.

[JohnTopley]

What happens if sum of column is greater than 40 i.e. over-allocated as can happen now: "Points remaining" is negative. Do want simple message stating "over" or"under" allocation?

=IF(40-sum(B2:b6)<0,"Points over-allocated","Points under-allocated")

[HurstUOB]

What happens if sum of column is greater than 40 i.e. over-allocated as can happen now: "Points remaining" is negative. Do want simple message stating "over" or"under" allocation?

=IF(40-sum(B2:b6)<0,"Points over-allocated","Points under-allocated")

I need a message to appear if they allocate too much or not enough. Exactly 40 points must be allocated.

[ChemistB]

Working off John's formula, to have the message show up only when over allocating or when all 5 have been allocated,
In C7
=IF(40-SUM(B2:B6)<0,"Points over-allocated",IF(AND(COUNTIF($B$2:$B$6,">0")=5,40-SUM(B2:B6)>0),"Points under-allocated",""))

[JohnTopley]

Is it possible to allocate 40 points only filling 4 rows: if so, COUNTIF will "fail".

[HurstUOB]

Is it possible to allocate 40 points only filling 4 rows: if so, COUNTIF will "fail".

One row will have to be left blank as whoever is scoring can't give themselves a score. So 'A' can not give 'A' any points.

[ChemistB]

You have an issue with your Data Validation. For all 5 cells, it requires that the minimum be < 9 and the Max be > 11. You cannot fulfill this requirement when you are entering your first value. I suggest you do not attach Data Validation to cell B2.

John, I assume everyone gets at least one point. I could be wrong.

[HurstUOB]

You have an issue with your Data Validation. For all 5 cells, it requires that the minimum be < 9 and the Max be > 11. You cannot fulfill this requirement when you are entering your first value. I suggest you do not attach Data Validation to cell B2.

John, I assume everyone gets at least one point. I could be wrong.

Sorry should of explained. I am trying to create a form which I would send to each member of a group for them to score their team mates. The rules are that no one can be scored over 15, one must be scored over 11, one must be scored less than 9, you can't give yourself a score and all 40 points must be allocated.

I received some help on an earlier thread with the first three rules but now my colleague tells me that all the points must be used as well.

[ChemistB]

Okay, so the formula would change to

=IF(40-SUM(B2:B6)<0,"Points over-allocated",IF(AND(COUNTIF($B$2:$B$6,">0")=4,40-SUM(B2:B6)>0),"Points under-allocated",""))

[HurstUOB]

Okay, so the formula would change to

=IF(40-SUM(B2:B6)<0,"Points over-allocated",IF(AND(COUNTIF($B$2:$B$6,">0")=4,40-SUM(B2:B6)>0),"Points under-allocated",""))

Thank you that's very helpful.

[ChemistB]

IF you want to include the criteria that a person cannot give points to themselves, you can modify the data validation to

=AND(MIN($B$2:$B$6)<9,MAX($B$2:$B$6)>11,MAX($B$2:$B$6)<=15,VLOOKUP($B$11, $A$2:$B$6,2,FALSE)=0)

[HurstUOB]

IF you want to include the criteria that a person cannot give points to themselves, you can modify the data validation to

=AND(MIN($B$2:$B$6)<9,MAX($B$2:$B$6)>11,MAX($B$2:$B$6)<=15,VLOOKUP($B$11, $A$2:$B$6,2,FALSE)=0)

Even better! Thanks!