Formula question w/couple criterias,using sumif

[calientepepper1976]

Hi,
I'm using sumif to look for a clientID with the same number and then returning the total value and adding them if there are more than 2. There are some that have more than one and I want the total not the 1st one so I used:
=SUMIF('2010'!$A$2:$A$967,$A16,'2010'!$AC$2:$AC$967)
Column A has Client ID's and column AC has a % of marketshare

This works for what I need, BUT there are a few that are at 100% with just one already so if there are more than 2 it brings back a value of 200%, 300% or 400%, etc. If it's more than 100%, I only want it to return a value of 100%.

Any suggestions?

Thank you!

 

[T. Valko]

Hi,
I'm using sumif to look for a clientID with the same number and then returning the total value and adding them if there are more than 2. There are some that have more than one and I want the total not the 1st one so I used:
=SUMIF('2010'!$A$2:$A$967,$A16,'2010'!$AC$2:$AC$967)
Column A has Client ID's and column AC has a % of marketshare

This works for what I need, BUT there are a few that are at 100% with just one already so if there are more than 2 it brings back a value of 200%, 300% or 400%, etc. If it's more than 100%, I only want it to return a value of 100%.

Any suggestions?

Thank you!

If I understand what you want, try this...

=MIN(100%,SUMIF('2010'!$A$2:$A$967,$A16,'2010'!$AC$2:$AC$967))

 

[calientepepper1976]

Yes, that worked perfectly. Thanks for the response and quick one at that.


THANKS!

 

[T. Valko]

Yes, that worked perfectly. Thanks for the response and quick one at that.


THANKS!

You're welcome. Thanks for the feedback!