Percentage total may not exceed 100
 

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

Percentage total may not exceed 100
 

"The Fool on the Hill" wrote in
message ...
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

Jay,

You don't add up percentages. A percentage is an expression of proportion
with a base of 100. It could be any other base but that is tradition so we
stick with that. You should be thinking not about how to add up percentages
but how to change the proportion (percentage) as new data is added.

Bill Ridgeway
Computer Solutions

Percentage total may not exceed 100
 

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

Percentage total may not exceed 100
 

Bill,

Thank you very much for your insight into this topic and thanks for taking
the time to reply to my question.

No matter what expression I use, whether percentage or something else. I am
looking for a formula to state whether a person has been overallocated.

Jay


"Bill Ridgeway" wrote:

"The Fool on the Hill" wrote in
message ...
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

Jay,

You don't add up percentages. A percentage is an expression of proportion
with a base of 100. It could be any other base but that is tradition so we
stick with that. You should be thinking not about how to add up percentages
but how to change the proportion (percentage) as new data is added.

Bill Ridgeway
Computer Solutions

Percentage total may not exceed 100
 

Hi,

Using your example
If column E was
Person 1
Person 2
Person 3
Person 4
Then put this in F1 and drag down to F4
=SUMIF($A$1:$A$5,E1,$B$1:$B$5)+SUMIF($C$1:$C$5,E1, $D$1:$D$5)

HTH
Martin


"The Fool on the Hill" wrote in
message ...
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

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