Jul 14 2021 04:33 AM
Hi,
I would like to change the color of a cell using sumproduct., or any other formula that does the job.
The color should change if number of employees is less than the value in E4 (2)
=SUMPRODUCt(--(t_bemanning[[Ansatt 1]:[Ansatt 11]]=Ukeplan!$B4)*(--t_bemanning[Dato]=Ukeplan!B2)) counts the right number of employees.
=SUMPROEDUCT(--(t_bemanning[[Ansatt 1]:[Ansatt 11]]=Ukeplan!$B4)*(--t_bemanning[Dato]=Ukeplan!B2))<$E$4 returns the right TRUE or FALSE.
But when i copy the fomula into the conditional formating command line, Excel says it is a problem with the formula. "are yout rying to enter a formula..."
Best Regards
- Geir
Jul 14 2021 04:47 AM
SolutionWithout 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?
Jul 15 2021 02:36 AM
Jul 14 2021 04:47 AM
SolutionWithout 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?