mizjackson
New Member
- Joined
- Jan 9, 2018
- Messages
- 1
I have a situation where I have 4 contract types and many clients have multiple contract types. I have created a spreadsheet with a "switchboard" so I can select the contract types I want to include in my sales estimates using "TRUE" or "FALSE". I can also set a percentage threshold based on the overall number of sales for each client.
Criteria 1: A percentage threshold of >= the percent I plug in cell AA2.
Criteria 2: The contract type must = "TRUE"
If both criteria are met, I want it to add-up the sales for each client, but only include the sales under the contracts which are "TRUE."
I have been trying to come up with a nested IF statement, SUMIF, or SUMPRODUCT formula that I can use to calculate the results for each client.
For example- John has 4 contract types: Apple, Banana, Pear, Pineapple.
Pear and Apple are identified as "TRUE" contract types and the others are identified as "FALSE."
If a John has a 60% or higher threshold in overall sales, I want to sum the sales specific only to his Pear and Apple contracts. SUMIFS are giving me an error, I believe because my ranges don't match in length.
Here are some other formulas I've tried:
=SUMIF(AC5:AF5,"TRUE",AC15:AF15) gives me the right result based on contract selection, but the client may not have met the 60% criteria.
=IF(SUMIF(Y13:Y314,">="&AA2),IF(OR(AC5="TRUE",AD5="TRUE",AE5="TRUE",AF5="TRUE"),SUM(AC15:AF15))) gives me the answer "FALSE" rather than the calculation of sales.
=IF(AND(($Y$11:$Y$314)>=$AA$2,(AC5:AF5)="TRUE"),SUM(AC15:AF15)),0) gives me 0 even though the client has met both criteria
=SUMPRODUCT(($Y$7:$Y$314>=$AA$2)*($AC$5:$AF$5="TRUE")*(AC15:AF15)) also gives me 0 even though the client has met both criteria
I've tried various versions of these, but they result in errors.
Any other ideas? I'm wondering if EXCEL is incapable of using text and numeral criteria simultaneously?
