User Profile
Jamil
Bronze Contributor
Joined Oct 14, 2016
User Widgets
Recent Discussions
Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?
GiadaBellan Hi again, Not sure if I fully understood the requirement. based on what I understood. I came up with an array formula. =SUMPRODUCT(--(($O4:$AK4)<=TRANSPOSE($G$8:$G$30))*($O4:$AK4<>""))+1 I used this formula in Cell L1 and dragged it down. If you do not have Excel 365, then the formula to be entered using Control + Shift + Enter Please see attached file.5.1KViews1like3CommentsRe: How to count and sum "Condtional formatting" cells by color in Excel 2010?
ajt8888 I looked at the file. the same issue as other users. the applied range of the conditional formatting and the range used inside UDF are not consistent. and also the CF rules are not set using formula.5.1KViews0likes0CommentsRe: How to count and sum "Condtional formatting" cells by color in Excel 2010?
GiadaBellan I looked at the file. it does not work because of two reasons, if you look at my earlier posts. the UDF has two limitations. A) the range you used in the UDF N4:AK4 and the Conditional formatting you used in for this range is not the same. there are multiple conditional formatting with different dimensions in in this range. B) the formulas that set the conditions of UDF are not set using formula but the "Format Cells that only contain" the UDF only works if formulas are set using "Use a formula to determine which cell to format". there are multiple conditions. I tried to understand the logic. perhaps built-in formula can be used instead of the UDF to return the same result you are looking for.5.1KViews0likes5Comments- 15KViews0likes0Comments
Re: sum by color when colors are set by conditional formatting
Hi GSMCONNECT you misunderstood on the use of the SUMPRODUCT formula. it was not meant to be used inside conditional formatting. it was meant to replace the UDF. I have now placed three formulas in cells B51, C51, and D51. the formulas work just fine, so you do not need to use UDF SumConditionColorCells plz see attached file with the formulas.5.2KViews0likes0CommentsRe: sum by color when colors are set by conditional formatting
HI GSMCONNECT, Yes, it could be the language issue, but I am not sure. in B51 you can use a formula and completely disregard the UDF. a formula to get that result is =SUMPRODUCT(($P$9:$P$18)*(ISNUMBER($P$9:$P$18))*(LEN($P$9:$P$18)=1)) and in Dutch =SOMPRODUCT(($P$9:$P$18)*(ISGETAL($P$9:$P$18))*(LENGTE($P$9:$P$18)=1))5.2KViews0likes2CommentsRe: sum by color when colors are set by conditional formatting
Hi GSMCONNECT, Mould Template1.xlsm is not my file, if you downloaded from one of the answers in the thread, it could be my reply to one of the questions in the thread. so, I did not ever create such file. However, I quickly looked at the file you shared. I do not get any #VALUE error. Perhaps, it is some Add-In or any other file in your excel start up folder that interferes with the UDF. Try to disable them and then the error may disappear.5.2KViews0likes4CommentsRe: sum by color when colors are set by conditional formatting
Hi bobbyfisher, I looked at your file and the reasony ou get the VALUE error, because of the two reasons. A) the conditional formats are not set using formulas B) the range inside UDF and the conditional formatted range are not consistent. If you look at my earlier posts, I have mentioned the three limitations of the UDF. so, unless those conditions are satisfied, the error will not go away.3.5KViews0likes0Comments- 3KViews0likes2Comments
Recent Blog Articles
No content to show