Forum Discussion
Conditional formating using sumproduct formula
Without a test workbook I cannot be sure but I would suggest moving the formula to a Defined Name e.g. 'underManned?'. Some aspects of formula evaluation are not as good as they could be within Conditional Formatting.
Other observations are that the coercion from TRUE/FALSE to 1/0 will take place when you multiply conditions so the '--' are surplus to requirements, and that a formula evaluated within Name Manager is always an array calculation (like 365) so SUM will work as well as SUMPRODUCT in that context.
This way the formula used for the conditional formatting is
= underManned?
Without a test workbook I cannot be sure but I would suggest moving the formula to a Defined Name e.g. 'underManned?'. Some aspects of formula evaluation are not as good as they could be within Conditional Formatting.
Other observations are that the coercion from TRUE/FALSE to 1/0 will take place when you multiply conditions so the '--' are surplus to requirements, and that a formula evaluated within Name Manager is always an array calculation (like 365) so SUM will work as well as SUMPRODUCT in that context.
This way the formula used for the conditional formatting is
= underManned?
- Perfect, using name manger solved it. And thank you for the clarification about SUM vs SUMPRODUCT.
Best regards
- Geir
