SOLVED

Conditional formating using sumproduct formula

Steel Contributor

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..."

 

sumproduct farge.PNG

 

Best Regards

- Geir

2 Replies
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

@Geir Hogstad 

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
1 best response

Accepted Solutions
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

@Geir Hogstad 

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?

View solution in original post