Percentage total may not exceed 100
Mike and Martin,
That is exactly what I am looking for !!
Great work !
Thank u !
Jay
"Mike H" wrote:
Maybe:-
Put this in a cell formatted as % and apply a conditional format to turn it
red if 100%
=SUMIF(A1:A5,"Person 1",B1:B5)+SUMIF(C1:C5,"Person 1",D1:D5)
Mike
"The Fool on the Hill" wrote:
Dear Excel(lent) users,
I want to add up percentages:
I have 4 columns (A, B, C, D). In
A: Name of a person (selectable from validation list)
B: Percentage
C: Name of a person (selectable from validation list, same list as A)
D: Percentage
You can select a person and add a percentage. This means that one person can
be selected multiple times.
I know want to control, that one person is not going to exceed 100%. Is
there some formula for this?
For example:
When in Column A a name is selected and a percentage is added in Column B
And you continue filling the details down the column. The percentages can be
added up. In a different field I want to check whether a person does not
exceed the 100% mark.
For instance:
A B C D
-------------------------------------------
Person 1 1 5% Person 2 10%
Person 2 25% Person 2 10%
Person 3 35% Person 1 10%
Person 4 45% Person 3 10%
Person 2 55% Person 4 10%
Now in a different column I want to check if for instance person 2 does not
exceed 100%. Now the formula needs to check All person 1's in A and C and
check what percentages they have behind their name and sum them up.
In this case person 2 occurs 4 times (2x in A and 2x in B) and his sumned
percentage is 25+55+10+10=100% (in this case it is ok).
Thanks for helping me out !
Kind regards,
Jay
|