SOLVED

How to count and sum "Condtional formatting" cells by color in Excel 2010?

%3CLINGO-SUB%20id%3D%22lingo-sub-116110%22%20slang%3D%22en-US%22%3EHow%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-116110%22%20slang%3D%22en-US%22%3E%3CP%3EI%20installed%20few%20add-ins%20for%20counting%20%22Conditional%20formatted%22%20cells%20but%20all%20are%20generating%20an%20instant%20(one-time)%20numeric%20result%20by%20activating%20that%20command.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I%20tried%20a%20code%20which%20gives%20an%20instant%20counting%20of%201%20picked%20color%20without%20a%20total%20sum%20of%20all%20other%20colors%20-in%20my%20case%2012%20colors%20in%20a%20matrix.%20(%22How%20to%20use%20the%20code%20to%20count%20colored%20cells%20and%20sum%20their%20values%22%20-%20%3CA%20href%3D%22https%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2013%2F12%2F12%2Fcount-sum-by-color-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2013%2F12%2F12%2Fcount-sum-by-color-excel%2F%3C%2FA%3E%20)%3C%2FP%3E%3CDIV%3E%3CDIV%20class%3D%22syntaxhighlighter%20nogutter%20%20vb%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22syntaxhighlighter%20nogutter%20%20vb%22%3EMy%20question%20is%3A%20Is%20there%20any%20functional%20code%20(or%20other%20aproach)%20which%20can%20count%20and%20sum%20all%20cells%20by%20%22Conditional%20formatted%22%20colors%2C%20on%20one%20sheet%2C%20and%20that%20the%20generated%20result%20is%20linked%20through%20common%20formulas%3F%20That%20can%20be%20updated%2Frefreshed%2Fcode%20run%20during%20the%20work%20process%20as%20a%20complete%20overview%20of%20colors%20of%20a%20matrix%20(and%20not%20by%20picking%20every%20color%20every%20time%20as%20I%20mentioned).%3C%2FDIV%3E%3CDIV%20class%3D%22syntaxhighlighter%20nogutter%20%20vb%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22syntaxhighlighter%20nogutter%20%20vb%22%3EThanks%20in%20advance!%3C%2FDIV%3E%3CDIV%20class%3D%22syntaxhighlighter%20nogutter%20%20vb%22%3E%3CBR%20%2F%3E%3CTABLE%20cellspacing%3D%220%22%20cellpadding%3D%220%22%20border%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-116110%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ecode%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EColors%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EConditional%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ecount%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esum%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-392172%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-392172%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20worked%20perfectly!%26nbsp%3B%20Thank%20you%20so%20very%20very%20much!%26nbsp%3B%20One%20more%20question...Is%20there%20any%20way%20to%20have%20the%20cells%20that%20have%20no%20data%20in%20them%20remain%20uncolored%3F%26nbsp%3B%20I%20was%20playing%20with%20the%20formulas%20but%20as%20I'm%20sure%20you've%20figured%20out%20by%20now%20I'm%20not%20the%20best%20at%20them.%26nbsp%3B%20Everything%20else%20is%20working%20beautifully%20and%20I%20am%20grateful%20for%20what%20I've%20got%20but...%26nbsp%3B%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390886%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390886%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F310457%22%20target%3D%22_blank%22%3E%40Laurafer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ethanks%20for%20your%20kind%20words.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20saw%20your%20workbook%20and%20there%20are%20many%20issues%20with%20the%20CF%20formulas%20you%20set.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDouble%20condition%20set%20for%20a%20single%20cell%20with%20contradicts%20each%20other.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eso%20example%20in%20the%20cell%20E28%20there%20are%20two%20formulas%20in%20CF%20and%20both%20of%20them%20contradicts%20each%20other.%3C%2FP%3E%0A%3CP%3E%3DIF(E28%26gt%3BD28%2C%22true%22%2C%22pass%22)%3CBR%20%2F%3E%3DIF(E28%3CC28%3E%0A%3C%2FC28%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eanother%20issue%20is%20that%20you%20set%20IF%20formulas%20to%20return%20test%20in%20CF%20which%20is%20not%20correct%20way%20to%20set%20formula.%3C%2FP%3E%0A%3CP%3Efor%20example%20if%20you%20want%20a%20condition%20to%20turn%20red%20if%20certain%20condition%20is%20met%20then%20you%20can%20simply%20use%20equal%20sign%20and%20there%20is%20no%20need%20for%20IF%20function%2C%20in%20fact%20IF%20function%20ruins%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Efor%20example%20if%20you%20want%20to%20turn%20cell%20E28%20to%20red%20if%20its%20value%20is%20greater%20than%20D28%20then%20you%20can%20simply%20use%20%3DE28%26gt%3BD28%26nbsp%3B%20simply%20as%20this.%20CF%20will%20return%20True%20if%20condition%20is%20met%20and%20false%20if%20it%20is%20not%20met.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20see%20attached%20your%20workbook.%3C%2FP%3E%0A%3CP%3EI%20have%20added%20two%20more%20UDFs%20so%20that%20you%20can%20debug%20and%20find%20the%20problem.%3C%2FP%3E%0A%3CP%3EThe%20first%20UDF%20return%20the%20formula%20used%20in%20the%20CF%20for%20that%20cell%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFunction%20CondFormulaformula(myCell%2C%20Optional%20cond%20As%20Long%20%3D%201)%20As%20String%3C%2FP%3E%0A%3CP%3ECondFormulaformula%20%3D%20myCell.FormatConditions(cond).Formula1%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ethis%20second%20formula%20evaluates%20the%20formula%20used%20in%20CF%20and%20returns%20the%20evaluated%20value.%3C%2FP%3E%0A%3CP%3EFunction%20CondFormula(myCell%2C%20Optional%20cond%20As%20Long%20%3D%201)%20As%20String%3C%2FP%3E%0A%3CP%3ECondFormula%20%3D%20Application.Evaluate(myCell.FormatConditions(cond).Formula1)%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyou%20can%20see%20in%20the%20column%20H%20which%20i%20highlighted%20in%20yellow.%26nbsp%3B%20the%20evaluated%20CF%20from%20your%20formulas%20and%20they%20are%20not%20consistent.%26nbsp%3B%20with%20the%20help%20of%20two%20addtional%20UDFs%20you%20can%20debug%20your%20CF%20formulas%20and%20get%26nbsp%3B%20the%20correct%20result.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHope%20this%20helps.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390751%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390751%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20morning%20Jamil!%26nbsp%3B%20This%20thread%20has%20been%20SO%20helpful!%26nbsp%3B%20I%20really%20appreciate%20it.%26nbsp%3B%20Of%20course%2C%20since%20I'm%20joining%20I'm%20having%20trouble%20lol.%3C%2FP%3E%3CP%3EI%20added%20the%20UDF%20and%20made%20sure%20that%20the%20CF%20I'm%20using%20is%20based%20on%20formulas%20however%20I%20can't%20seem%20to%20get%20the%20results%20to%20be%20stable.%3C%2FP%3E%3CP%3EThis%20is%20a%20fairly%20simple%20application%20and%20I'm%20feeling%20pretty%20dumb%20right%20now%20as%20I'm%20sure%20whatever%20it%20is%20I'm%20doing%20wrong%20is%20an%20easy%20fix.%3C%2FP%3E%3CP%3EWould%20you%20mind%20terribly%20taking%20a%20look%3F%26nbsp%3B%20I%20tried%20a%20couple%20different%20formulas%20in%20the%20CF%20but%20even%20the%20straight-forward%20ones%20don't%20always%20end%20in%20the%20correct%20result.%26nbsp%3B%20Sometimes%20the%20count%20is%20working%20and%20sometimes%20it%20isn't.%3C%2FP%3E%3CP%3EAny%20help%20you%20can%20give%20me%20would%20be%20much%20appreciated.%26nbsp%3B%20This%20is%20making%20me%20nuts%20lol.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353537%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353537%22%20slang%3D%22en-US%22%3EIt%20should%20work.%20i%20suspect%20there%20must%20be%20an%20issue%20with%20the%20way%20CF%20range%20is%20set.%20%3CBR%20%2F%3Eif%20you%20upload%20a%20dummy%20sample%2C%20i%20can%20take%20a%20look.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352737%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352737%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20I%20use%20the%20formula%2C%20it%20works%20rows%20in%20the%20column%20except%20for%20the%202nd%20row%20of%20data.%26nbsp%3B%20It%20does%20not%20count%20that%20row.%26nbsp%3B%20Has%20anyone%20hit%20this%20issue%20or%20found%20a%20way%20around%20it%3F%26nbsp%3B%20It%20correctly%20counts%20all%20of%20the%20other%20rows%20so%20I%20know%20it%20is%20looking%20at%20the%20condition%20formatting.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338980%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338980%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Alin_20.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20did%20not%20properly%20set%20your%20conditional%20formatting%20in%20your%20file%2C%20that%20is%20why%20it%20was%20not%20working.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20set%20the%20conditional%20formatting%20in%20the%20attached%20file%20correctly%20and%20it%20works%20now.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eplz%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-331436%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-331436%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%2C%3C%2FP%3E%3CP%3EThe%20example%20that%20you%20helped%20me%20with%20worked%20like%20a%20charm%20but%20only%20for%20one%20conditional%20format%20formula%20applied%20for%20a%20range%20of%20cells.%3C%2FP%3E%3CP%3ENow%20i%20have%20a%20case%20in%20which%20i%20want%20to%20count%20all%20the%20CF%20cells%20in%20a%20row%2C%20but%20with%20multiple%20CF%20formulas.%3C%2FP%3E%3CP%3EI%20followed%20all%20the%20steps%20that%20you%20suggested%20(i%20hope%20i%20didn't%20missed%20something%20out).%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3C%2FP%3E%3CP%3EAlin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-326179%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326179%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Alin20%3C%2FP%3E%3CP%3Ethank%20you%20very%20much%20for%20your%20answer%20managed%20to%20solve%20my%20problem.%20I%20realized%20that%20there%20were%20a%20lot%20of%20conditional%20formatting%20and%20could%20not%20count%20the%20colors%20correctly.%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20help%20again.%3C%2FP%3E%3CP%3EHi%2C%3CBR%20%2F%3Enorbi%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321541%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321541%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Norbi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20respond%20to%20your%20post%20due%20to%20the%20fact%20that%20i%20had%20the%20same%20challenge%20as%20you%2C%20and%20Jamil%20showed%20me%20the%20way%20around%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20check%20the%20previous%20post%20in%20which%20Jamil%20replied%20to%20me%20you%20will%20see%20that%20the%20CF%20range%20need%20to%20be%20exactly%20the%20row%20for%20which%20you%20need%20to%20make%20the%20count.%20you%20cannot%20use%20a%20range%20like%20A3%3AG16%20because%20it%20won't%20work.%20The%20correct%20way%20to%20do%20it%20is%20to%20set%20like%20this%3A%20A3%3AG3.%20-%20for%20the%20first%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20afterwards%20you%20need%20to%20use%20the%20format%20painter%20and%20to%20paste%20the%20CF%20formulas%20row%20by%20row.%20As%20a%20tip%2C%20if%20you%20want%20to%20make%20multiple%20formats%20with%20the%20format%20painter%20you%20can%20double%20click%20the%20format%20painter%20and%20it%20will%20remain%20active%20for%20all%20the%20pastes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJamil%20also%20made%20a%20video%20with%20this%20step%20in%20the%20post%20he%20replied%20to%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20attached%20the%20modified%20workbook%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20hope%20it%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321112%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321112%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20evening%20Mr.%20Jamil%20Mohammad%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20read%20your%20post%20and%20downloaded%20it.%20It%20works%20well.%3CBR%20%2F%3EBut%20I%20would%20have%20a%20question%20of%20how%20to%20solve%20each%20line%20separately%20for%20each%20color.%3CBR%20%2F%3EThank%20you%20for%20your%20reply%20and%20help%20in%20advance%2C%20a%20nice%20evening.%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320699%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320699%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%2C%3C%2FP%3E%3CP%3EThe%20example%20that%20you%20helped%20me%20with%20worked%20like%20a%20charm%20but%20only%20for%20one%20conditional%20format%20formula%20applied%20for%20a%20range%20of%20cells.%3C%2FP%3E%3CP%3ENow%20i%20have%20a%20case%20in%20which%20i%20want%20to%20count%20all%20the%20CF%20cells%20in%20a%20row%2C%20but%20with%20multiple%20CF%20formulas.%3C%2FP%3E%3CP%3EI%20followed%20all%20the%20steps%20that%20you%20suggested%20(i%20hope%20i%20didn't%20missed%20something%20out).%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3C%2FP%3E%3CP%3EAlin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-316114%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-316114%22%20slang%3D%22en-US%22%3EHi%20Roland%2C%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20read%20through%20my%20earlier%20posts.%20I%20have%20clearly%20mentioned%20that%20UDF%20only%20works%20if%20the%20conditional%20formating%20is%20set%20using%20Formula.%20you%20have%20used%20formulas%20and%20non-formula%20options%20to%20set%20your%20conditional%20formatting.%20that%20is%20the%20first%20issue%20that%20is%20causing%20the%20UDF%20not%20to%20work.%3CBR%20%2F%3E%3CBR%20%2F%3Ethe%20second%20issue%20is%20that%20your%20are%20using%20the%20whole%20column%20references%20in%20your%20CF%20which%20is%20not%20going%20to%20work.%3CBR%20%2F%3E%3CBR%20%2F%3Ethird%20issue%20is%20that%20your%20CF%20is%20set%20by%20column%20and%20you%20are%20trying%20to%20do%20the%20count%20in%20UDF%20using%20row.%20If%20you%20look%20at%20the%20example%20I%20illustrated%20in%20answer%20to%20Alin.%20you%20can%20see%20that%20i%20modified%20the%20CR%20using%20painbrush%20to%20reflect%20the%20rows.%20%3CBR%20%2F%3E%3CBR%20%2F%3Eso%20you%20need%20to%20fix%20the%20three%20of%20the%20issues%20i%20pointed%2C%20so%20that%20the%20UDF%20would%20work.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-315144%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315144%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20my%20attached%20file%20which%20i%20want%20to%20count%20the%20colored%20CF%20cells%20by%20row.%20Problem%20is%20there%20are%20different%20formulas%20in%20each%20column%2C%20if%20i%20want%20to%20count%20the%20result%20by%20rows%2C%20i%20get%20an%20error.%20If%20i%20count%20by%20column%20wise%20it%20got%20results.%20Please%20help%20so%20i%20can%20count%20it%20by%20rows.%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313672%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313672%22%20slang%3D%22en-US%22%3E%3CP%3Ethank%20you%20so%20much%20for%20this%20Jamil%3C%2FP%3E%3CP%3EI%20got%20it%20finally%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wish%20you%20all%20the%20best%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313653%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313653%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%20Alin%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20looked%20at%20your%20file.%26nbsp%3B%20The%20issue%20was%20that%20you%20had%20CF%20applied%20range%20A10%20to%26nbsp%3BA10%3AE12%26nbsp%3B%20while%20your%20UDF%20were%20using%20for%20each%20row.%26nbsp%3B%20the%20UDF%20range%20and%20the%20CF%20range%20should%20match%2C%20that%20is%20when%20it%20will%20work.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20attached%20the%20workbook%20and%20also%20i%20have%20attached%20a%20recorded%20video%20on%20how%20to%20use%20the%20paintbrush%20to%20apply%20CF%20to%20the%20other%20cells%20without%20the%20hassle%20of%20recreating%20CF%20for%20each%20row.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313620%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313620%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20used%20one%20of%20the%20worksheets%20that%20i%20found%20on%20this%20topic%2C%20on%20which%20the%20count%20by%20color%20worked%2C%20and%20i%20adapted%20it%20for%20my%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20used%20a%20formula%20in%20conditional%20formatting%20in%20order%20to%20highlight%20all%20the%20cells%20that%20contain%20%22TEXT%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20somehow%20it%20is%20showing%20only%20a%20count%20of%20three%20for%20all%20the%20three%20rows%20that%20i%20used%20for%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20missed%20something%20for%20sure.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20the%20worksheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313606%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313606%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Alin%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20checked%20one%20of%20your%20file%20and%20it%20is%20clear%20that%20you%20did%20not%20use%20the%20option%20of%20CF%20%22-%20Use%20a%20formula%20to%20determine%20which%20cells%20to%20format%22%26nbsp%3B%20%26nbsp%3B%20the%20UDF%20only%20works%20if%20you%20used%20formula%20to%20determine%20which%20cells%20to%20format.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313598%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313598%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20quite%20a%20particular%20situation%20regarding%20the%20count%20of%20CF%20cells%2C%20apart%20from%20what%20i%20saw%20exemplified%20in%20this%20topic.%20(i%20have%20tested%20all%20the%20examples%20in%20this%20topic%20for%20my%20application%20and%20didn't%20seemed%20to%20work)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20I%20have%20a%20worksheet%20that%20automatically%20color%20some%20cells%20using%20Conditional%20Formatting%20(some%20CF%20colored%20cells%20contain%20text%2C%20and%20some%20others%20CF%20colored%20cells%20are%20blank)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20test%20sheet%20with%20the%20structure.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%20to%20mention%20is%20the%20fact%20that%20in%20column%20%22H%22%20i%20will%20have%20to%20count%20the%20CF%20cells%20for%20maybe%20thousands%20of%20rows%20and%20to%20be%20able%20to%20see%20the%20count%20for%20each%20row%20in%20part.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%2C%3C%2FP%3E%3CP%3EAlin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313597%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313597%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20quite%20a%20particular%20situation%20regarding%20the%20count%20of%20CF%20cells%2C%20apart%20from%20what%20i%20saw%20exemplified%20in%20this%20topic.%20(i%20have%20tested%20all%20the%20examples%20in%20this%20topic%20for%20my%20application%20and%20didn't%20seemed%20to%20work)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20I%20have%20a%20worksheet%20that%20automatically%20color%20some%20cells%20using%20Conditional%20Formatting%20(some%20CF%20colored%20cells%20contain%20text%2C%20and%20some%20others%20CF%20colored%20cells%20are%20blank)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20test%20sheet%20with%20the%20structure.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%20to%20mention%20is%20the%20fact%20that%20in%20column%20%22H%22%20i%20will%20have%20to%20count%20the%20CF%20cells%20for%20maybe%20thousands%20of%20rows%20and%20to%20be%20able%20to%20see%20the%20count%20for%20each%20row%20in%20part.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%2C%3C%2FP%3E%3CP%3EAlin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309090%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309090%22%20slang%3D%22en-US%22%3Emecerrato%20%3CBR%20%2F%3Efirst%20try%20to%20check%20that%20your%20workbook%20calculation%20setting%20is%20%22Automatic%22%20%3CBR%20%2F%3Eif%20it%20is%20automatic%20and%20still%20did%20not%20work%20then%20try%20putting%20the%20below%20line%20after%20the%20first%20line%20of%20UDF.%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.Volatile%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309089%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309089%22%20slang%3D%22en-US%22%3E7_Heaven%2C%3CBR%20%2F%3Eyou%20can%20use%20search%20function%2C%20but%20your%20ranges%20should%20not%20be%20whole%20column%20reference.%20I%20have%20shown%20in%20the%20example%20in%20my%20previous%20post%20how%20you%20can%20use%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308744%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308744%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%20I%20was%20able%20to%20successfully%20use%20your%20code%20to%20count%20my%20conditionally%20formatted%20cells%20but%20my%20data%20changes%20regularly%20and%20the%20function%20doesn't%20automatically%20refresh.%20In%20order%20for%20it%20to%20pick%20up%20the%20changes%20i%20have%20to%20refresh%20the%20cell%20with%20F2%20and%20then%20enter%2C%20any%20solution%20for%20that%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-305639%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-305639%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20believe%20my%20luck%20that%20you%20actually%20replied%20to%20my%20query!%20Thank%20you%20very%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20modified%20the%20formula%20as%20per%20your%20suggestion%20and%20remove%20the%20search%20function%2C%20it%20doesn't%20work%20on%20my%20master%20spreadsheet.%20Is%20there%20a%20way%20to%20modify%20the%20UDF%20to%20make%20it%20work%20with%20search%20function%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-304169%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-304169%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%207%26nbsp%3BHeven%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20have%20used%20Search%20function%20and%20also%20whole%20column%20reference%20A%3AA%20which%20is%20not%20compatible%20with%20the%20UDF.%26nbsp%3B%20%26nbsp%3BI%20have%20modified%20the%20formula%20and%20range%20in%20the%20attached%20example%20and%20the%20UDF%20works.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-301828%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-301828%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20stumbled%20upon%20your%20code%20as%20I%20am%20using%20conditional%20formatting%20formula%20to%20highlights%20my%20cell.%20Now%20I%20want%20to%20count%20the%20highlighted%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimilar%20to%20Julie%2C%20the%20VBA%20returns%20%22no%20color%22%20instead%20of%20the%20numbers.%20I%20have%20done%20everything%20that%20I%20can%20possibly%20can.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suspect%20it%20doesn't%20work%20because%20I%20use%20this%20formula%20in%20my%20conditional%20formatting%20%3Dif(isblank(%24K%241)%2C0%2C(search(%24K%241%2CA5))).%20A5%20is%20where%20my%20data%20starts.%20K1%20is%20where%20when%20someone%20type%20a%20text%20and%20that%20text%20is%20in%20the%20data%2C%20it%20will%20highlight%20those%20texts.%20Attached%20is%20an%20example%20(which%20didn't%20quite%20work%20but%20you'll%20get%20the%20idea).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-278838%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-278838%22%20slang%3D%22en-US%22%3EHi%20Mathew%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20did%20not%20understand%20your%20question.%20The%20attachment%20is%20list%20of%20acronyms%20in%20pdf.%20%3CBR%20%2F%3Eyour%20Excel%20file%20which%20you%20posted%20earlier%20does%20not%20contain%20of%20all%20of%20these.%20%3CBR%20%2F%3Eyou%20question%20was%20related%20to%20counting%20the%20green%20and%20red%20and%20to%20make%20this%20work%2C%20you%20need%20to%20set%20up%20the%20conditional%20formatting%20by%20yourself.%20then%20you%20can%20give%20me%20the%20list%20of%20the%20acronyms%20that%20are%20in%20green%20category%20and%20also%20the%20list%20which%20are%20in%20red%20category.%20by%20then%20i%20will%20be%20able%20to%20give%20you%20a%20formula%20that%20handles%20that%20count%20for%20red%20and%20green.%20Right%20now%2C%20I%20have%20not%20received%20sufficient%20information%20related%20to%20your%20excel%20problem.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276980%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276980%22%20slang%3D%22en-US%22%3E%3CP%3EThese%20are%20all%20the%20acronyms%20I%20am%20trying%20to%20achieve%20on%20my%20spreadsheet%2C%20thank%20you%20for%20your%20assistance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BMatthew%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276928%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276928%22%20slang%3D%22en-US%22%3EHi%20Mathew%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20looked%20at%20your%20file.%20You%20have%20set%20up%20the%20conditional%20formatting%20using%20the%20%3D%20cell%20value%20option%20.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20UDF%20only%20works%20if%20the%20conditional%20formatting%20is%20set%20using%20formulas%20not%20through%20the%20built-in%20option%20of%20conditional%20formatting.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20can%20read%20about%20it%20here%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fsum-by-color-when-colors-are-set-by-conditional-formatting%2Ftd-p%2F36495%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fsum-by-color-when-colors-are-set-by-conditional-formatting%2Ftd-p%2F36495%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20you%20are%20trying%20to%20do%20is%20possible%2C%20but%20you%20have%20set%20many%20conditional%20formatting%20rules.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20can%20help%20you%20if%20you%20give%20me%20the%20list%20of%20the%20text%20that%20are%20for%20green%20color%20and%20the%20list%20of%20the%20texts%20that%20are%20for%20red%20color.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3Ethese%20are%20all%20of%20the%20list%20and%20if%20you%20can%20give%20me%20which%20one%20of%20them%20are%20for%20green%20and%20which%20one%20for%20red.%20I%20can%20build%20the%20formula%20for%20you.%3CBR%20%2F%3E%3CBR%20%2F%3EAcceptable%20Use%20Policy%3CBR%20%2F%3EAccess%20Control%20List%3CBR%20%2F%3EAccess%20Point%3CBR%20%2F%3EAddress%20Resolution%20Protocol%3CBR%20%2F%3EAddress%20Space%20Layout%20Randomization%3CBR%20%2F%3EAdvanced%20Encryption%20Standard%3CBR%20%2F%3EAdvanced%20Encryption%20Standard%20256%20bit%3CBR%20%2F%3EAdvanced%20Persistent%20Threat%3CBR%20%2F%3EAnnualized%20Loss%20Expectancy%3CBR%20%2F%3EAnnualized%20Rate%20of%20Occurrence%3CBR%20%2F%3EAnti-virus%3CBR%20%2F%3EApplication%20Programming%20Interface%3CBR%20%2F%3EApplication%20Service%20Provider%3CBR%20%2F%3EAttribute-based%20Access%20Control%3CBR%20%2F%3EAuthentication%20Header%3CBR%20%2F%3EAuthentication-Authorization-Accounting%3CBR%20%2F%3EBasic%20Input%2FOutput%20System%3CBR%20%2F%3EBridge%20Protocol%20Data%20Unit%3CBR%20%2F%3EBring%20Your%20Own%20Device%3CBR%20%2F%3EBusiness%20Availability%20Center%3CBR%20%2F%3EBusiness%20Continuity%20Planning%3CBR%20%2F%3EBusiness%20Impact%20Analysis%3CBR%20%2F%3EBusiness%20Partners%20Agreement%3CBR%20%2F%3ECertificate%3CBR%20%2F%3ECertificate%20Authority%3CBR%20%2F%3EChallenge%20Handshake%20Authentication%20Protocol%3CBR%20%2F%3EChief%20Information%20Officer%3CBR%20%2F%3ECipher%20Block%20Chaining%3CBR%20%2F%3ECipher%20Feedback%3CBR%20%2F%3EClosed-Circuit%20TeleVision%3CBR%20%2F%3ECommon%20Access%20Card%3CBR%20%2F%3ECompletely%20Automated%20Public%20Turing%20Test%20to%20Tell%20Computers%20and%20Humans%20Apart%3CBR%20%2F%3EComputer%20Emergency%20Response%20Team%3CBR%20%2F%3EComputer%20Incident%20Response%20Team%3CBR%20%2F%3EContent%20Management%20System%3CBR%20%2F%3EContingency%20Planning%3CBR%20%2F%3EContinuity%20of%20Operations%20Plan%3CBR%20%2F%3EController%20Area%20Network%3CBR%20%2F%3ECorporate%20Owned-Personally%20Enabled%3CBR%20%2F%3ECorrective%20Action%20Report%3CBR%20%2F%3ECounter-Mode%2FCBC-Mac%20Protocol%3CBR%20%2F%3ECyclical%20Redundancy%20Check%3CBR%20%2F%3ETriple%20Digital%20Encryption%20Standard%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276557%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276557%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20tried%20your%20formulas%20and%20they%20do%20not%20work.%26nbsp%3B%20I%20have%20been%20stuck%20on%20this%20for%203%20weeks%20now%20and%20I'm%26nbsp%3Btrying%20to%20count%20my%20cells%20as%20they%20turn%20certain%20colors%20from%20Conditional%20Formatting%20rules%20I%20put%20in%20place%20for%20my%20drop%20down%20menu's.%26nbsp%3B%20If%20you%20would%20be%20able%20to%20assist%20me%20that%20would%20be%20wonderful%20because%20I%20am%20getting%20tired%20of%20watching%20and%20reading%20videos%20on%20how%20it%20wont%20work%20for%20me%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMatthew%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276281%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276281%22%20slang%3D%22en-US%22%3EHi%20Rowan%2C%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20need%20to%20save%20as%20macro%20enabled%20workbook%2C%20when%20you%20save%20as%20normal%20workbook%20format%20the%20UDF%20disappears.%3CBR%20%2F%3E%3CBR%20%2F%3EUse%20either%20macro%20enabled%20workbook%20format%20on%20save%20as%20it%20binary%20format.%20Then%20it%20will%20work.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20need%20to%20paste%20the%20code%20again%20into%20the%20workbook%20and%20save%20as%20then%20the%20Name%20error%20will%20fix.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276277%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276277%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20had%20a%20similar%20issue%20to%20Julie.%3C%2FP%3E%3CP%3EI%20did%20exactly%20what%20you%20said%20and%20the%20document%20worked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20saving%2C%20closing%20the%20document%20and%20today%20opening%20it%20the%20formula%20doesn't%20seem%20to%20work%20when%20calculating%20the%20colored%20cells.%3C%2FP%3E%3CP%3EI%20get%20the%20'%23NAME'%20error.%3C%2FP%3E%3CP%3EAny%20assistance%20you%20can%20offer%20would%20be%20appreciated...%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDocument%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-271275%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271275%22%20slang%3D%22en-US%22%3E%3CP%3Eyou%20have%20a%20new%20question%2C%20so%20the%20experts%20would%20likely%20to%20answer%20your%20question%20over%20there.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-271271%22%20slang%3D%22en-US%22%3Eokay%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271271%22%20slang%3D%22en-US%22%3E%3CP%3Eokay%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268398%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268398%22%20slang%3D%22en-US%22%3EPlease%20read%20my%20earlier%20messages%20on%20the%20thread%20and%20use%20the%20UDF%20to%20be%20able%20to%20count%20the%20conditionally%20formatted%20cells.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-264599%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-264599%22%20slang%3D%22en-US%22%3E%3CP%3EHello..%20can%20you%20solve%20the%20problem%20for%20attached%20file%3F%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20count%20the%20specific%20text%20colored%20by%20conditional%20formatting%20column%20wise.%20Conditional%20formatting%20done%20by%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-242160%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-242160%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241439%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241439%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20click%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20510px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F44725i9E2AE04F354DD6F8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eat%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241333%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241333%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20i%20open%20a%20new%20question%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241272%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241272%22%20slang%3D%22en-US%22%3EOpen%20a%20new%20question%20not%20on%20this%20thread%20but%20a%20new%20question%20with%20sample%20dummy%20of%20your%20data.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241246%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241246%22%20slang%3D%22en-US%22%3E%3CP%3EBad%20luck!%3C%2FP%3E%3CP%3EIs%20there%20any%20solution%20for%20my%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241243%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241243%22%20slang%3D%22en-US%22%3EHi%20aa%20aaaa%2C%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20read%20my%20earlier%20messages%2C%20the%20UDF%20only%20works%20if%20the%20conditional%20formatting%20is%20set%20using%20formula.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241237%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241237%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Jamil.%20but%20this%20function%20doesn't%20work%20in%20case%20of%20%22Top%2010%20Item%22%20conditional%20formatting.%20Do%20you%20have%20any%20idea%20for%20my%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227574%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227574%22%20slang%3D%22en-US%22%3EHi%20Haziyatul%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20checked%20your%20file.%20The%20range%20where%20the%20conditional%20formatting%20is%20applied%20and%20the%20range%20where%20the%20UDF%20uses%20have%20to%20be%20the%20same%20and%20they%20are%20not%20the%20same%20in%20your%20excel%20file.%3CBR%20%2F%3E%3CBR%20%2F%3ECF%20uses%20%24M%249%3A%24EA%2456%20and%20UDF%20uses%20X9%3AY13%3CBR%20%2F%3E%3CBR%20%2F%3Eso%20to%20make%20this%20work%2C%20you%20need%20to%20either%20exactly%20set%20CF%20for%20each%20of%20the%20vertical%20lines.%3CBR%20%2F%3Ehowever%2C%20you%20would%20not%20need%20to%20use%20the%20UDF%20for%20this%2C%20you%20can%20simply%20achieve%20the%20same%20result%20by%20using%20the%20SUMIF%20or%20SUMPRODUCT%20formula.%20you%20can%20see%20the%20examples%20in%20my%20earlier%20posts%20in%20the%20same%20thread.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227362%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227362%22%20slang%3D%22en-US%22%3E%3CP%3Ethe%20code%20doesnt%20work%20with%20my%20file..can%20you%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-209239%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-209239%22%20slang%3D%22en-US%22%3Enull%20null%3CBR%20%2F%3E%3CBR%20%2F%3Eyou%20asked%20the%20question%20in%20two%20places%2C%20so%20i%20replied%20here%20on%20why%20the%20UDF%20did%20not%20work%20for%20you.%20%3CBR%20%2F%3E%20please%20see%20my%20post%20here.%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FMacros-and-VBA%2Fsum-by-color-when-colors-are-set-by-conditional-formatting%2Ftd-p%2F36495%2Fhighlight%2Ftrue%2Fpage%2F2%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FMacros-and-VBA%2Fsum-by-color-when-colors-are-set-by-conditional-formatting%2Ftd-p%2F36495%2Fhighlight%2Ftrue%2Fpage%2F2%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207764%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207764%22%20slang%3D%22en-US%22%3E%3CP%3Ehaii%20i%20already%20try%20this%20but%20not%20successful%20la..%20please%20help%20me.%3C%2FP%3E%3CP%3EPlease%20look%20at%20my%20attachment%20and%20my%20problem%20at%20sheet%26nbsp%3BZONE_WK25(CountColor)%20and%20at%26nbsp%3B%20column%20AV10%20%3Dcountconditioncolorcells(%24D%2410%3A%24AS%2410%2CAV5)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-196751%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-196751%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Michelle%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20is%20a%20longer%20thread%20on%20the%20same%20UDF%2C%20there%20are%20multiple%20things%20that%20can%20cause%20the%20return%20of%20%22no%20color%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20read%20the%20limitations%20of%20the%20UDF%20and%20other%20comments%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FMacros-and-VBA%2Fsum-by-color-when-colors-are-set-by-conditional-formatting%2Ftd-p%2F36495%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FMacros-and-VBA%2Fsum-by-color-when-colors-are-set-by-conditional-formatting%2Ftd-p%2F36495%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-196491%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-196491%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20tried%20to%20use%20the%20SUM%20formula%20here%2C%20but%20keep%20getting%20%22NO%20COLOR%22...I%20read%20through%20all%20your%20posts%20and%20tried%20to%20troubleshoot%2C%20but%20can't%20figure%20out%20what%20I'm%20doing%20wrong...can%20you%20take%20a%20look%20for%20me%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-173259%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-173259%22%20slang%3D%22en-US%22%3EYou%20are%20welcome.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-173234%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-173234%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20Jamil!%20%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20seemed%20like%20something%20was%20actually%20wrong%20with%20my%20original%20dropdown%20list%2C%20so%20took%20some%20time%20for%20me%20to%20understand%20why%20it%20didn't%20work.%20But%20now%20it%20is%20finally%20ok%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%3CBR%20%2F%3EAll%20the%20best!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-173122%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-173122%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F129454%22%20target%3D%22_blank%22%3EJulie%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20checked%20your%20file.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eit%20was%20not%20working%20because%20A)%20you%20need%20to%20enable%20macros%20in%20order%20to%20be%20able%20to%20use%20the%20UDF.%3C%2FP%3E%0A%3CP%3EB)%26nbsp%3B%20the%20sample%20file%20you%20upload%2C%20you%20had%20only%20cells%20for%20criteria%20colored%2C%20but%20there%20wasn't%20any%20conditional%20formatting%20set%20in%20your%20workbook%2C%20that's%20why%20It%20did%20not%20work.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20recorded%20a%20video%20that%20shows%20how%20you%20can%20set%20up%20your%20conditional%20formatting.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eplease%20download%20the%20attached%20video%20and%20watch%20it.%20also%20i%20have%20attached%20your%20workbook%20with%20conditional%20formatting%20set%20in%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20class%3D%22user-login%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20class%3D%22user-login%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-173116%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-173116%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%2C%3C%2FP%3E%0A%3CP%3ENot%20sure%20why%20it%20is%20not%20working%20on%20my%20computer%2C%20because%20after%20pressing%20%22edit%22%20on%20Your%20document%2C%20it%20also%20got%20error%20value%20%22%23Name%3F%22%2C%20and%20You%20can%20see%20on%20my%20document%20Attached%20how%20it%20looks%20with%20error%20%23VALUE!%20I%20wasn't%20able%20to%20attach%20the%20Excel%20file%20With%20Makro%20enabled%2C%20but%20have%20of%20course%20had%20the%20makro%20enabled%20file%20so%20far.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGreat%20if%20you%20can%20advice.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3CNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-noscript-container%22%3E%3CDIV%20class%3D%22lia-spoiler-noscript-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FNOSCRIPT%3E%3C%2FDIV%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-173110%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-173110%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Julie%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eplease%20see%20attached%20workbook%20sample%20that%20shows%20how%20you%20can%20use%20the%20UDF.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eplease%20post%20back%2C%20if%20you%20have%20any%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-173094%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-173094%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3BJamil%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20You%20for%20posting%20this%20solution%2C%20I%20hope%26nbsp%3Bthat%20this%20will%20also%26nbsp%3Bsolve%20my%20problem%20With%20counting%26nbsp%3Bthe%26nbsp%3Bcolored%26nbsp%3Bcells%26nbsp%3Bwhen%20using%20conditinal%20formatting.%3C%2FP%3E%0A%3CP%3EHowever%2C%20so%20far%20I%20am%20not%20able%20to%20get%20a%20number%20Count%2C%20I%20only%20get%20%22NO-COLOR%22.%20E.g.%20%3DCountConditionColorCells(E2%3BE2%3AE5)%20or%20%3DCountConditionColorCells(E2%3AE5%3BE2)%20Perhaps%20I'm%20completely%20far%20out...%20Could%20You%20please%20let%20me%20know%20how%20the%20formula%20should%20be%20written%3F%3C%2FP%3E%0A%3CP%3ERegards%2C%3C%2FP%3E%0A%3CP%3EJulie%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-168023%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-168023%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20most%20welcome.%20Thanks%20for%20the%20feedback.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-167891%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-167891%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much%20for%20you%20kind%20and%20very%20helpful%20answer.%3C%2FP%3E%0A%3CP%3EI%20will%20try%20the%20explained%20method%20to%20finally%20resolve%20my%20issue.%3C%2FP%3E%0A%3CP%3EThanks%20and%20have%20a%20great%20day%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-165133%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-165133%22%20slang%3D%22en-US%22%3E%3CP%3EI%20came%20across%20this%20post%20being%20unanswered.%20so%20here%20is%20a%20User%20Defined%20Function%20in%20VBA%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eto%20SUM%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Euse%20this%20UDF%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EFunction%20SumConditionColorCells(CellsRange%20As%20Range%2C%20ColorRng%20As%20Range)%0ADim%20Bambo%20As%20Boolean%0ADim%20dbw%20As%20String%0ADim%20CFCELL%20As%20Range%0ADim%20CF1%20As%20Single%0ADim%20CF2%20As%20Double%0ADim%20CF3%20As%20Long%0ABambo%20%3D%20False%0AFor%20CF1%20%3D%201%20To%20CellsRange.FormatConditions.Count%0AIf%20CellsRange.FormatConditions(CF1).Interior.ColorIndex%20%3D%20ColorRng.Interior.ColorIndex%20Then%0ABambo%20%3D%20True%0AExit%20For%0AEnd%20If%0ANext%20CF1%0ACF2%20%3D%200%0ACF3%20%3D%200%0AIf%20Bambo%20%3D%20True%20Then%0AFor%20Each%20CFCELL%20In%20CellsRange%0Adbw%20%3D%20CFCELL.FormatConditions(CF1).Formula1%0Adbw%20%3D%20Application.ConvertFormula(dbw%2C%20xlA1%2C%20xlR1C1)%0Adbw%20%3D%20Application.ConvertFormula(dbw%2C%20xlR1C1%2C%20xlA1%2C%20%2C%20ActiveCell.Resize(CellsRange.Rows.Count%2C%20CellsRange.Columns.Count).Cells(CF3%20%2B%201))%0AIf%20Evaluate(dbw)%20%3D%20True%20Then%20CF2%20%3D%20CF2%20%2B%20CFCELL.Value%0ACF3%20%3D%20CF3%20%2B%201%0ANext%20CFCELL%0AElse%0ASumConditionColorCells%20%3D%20%22NO-COLOR%22%0AExit%20Function%0AEnd%20If%0ASumConditionColorCells%20%3D%20CF2%0AEnd%20Function%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20you%20want%20to%20Count%20instead%20of%20SUM%20then%20use%20the%20below%20UDF%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EFunction%20COUNTConditionColorCells(CellsRange%20As%20Range%2C%20ColorRng%20As%20Range)%0ADim%20Bambo%20As%20Boolean%0ADim%20dbw%20As%20String%0ADim%20CFCELL%20As%20Range%0ADim%20CF1%20As%20Single%0ADim%20CF2%20As%20Double%0ADim%20CF3%20As%20Long%0ABambo%20%3D%20False%0AFor%20CF1%20%3D%201%20To%20CellsRange.FormatConditions.Count%0AIf%20CellsRange.FormatConditions(CF1).Interior.ColorIndex%20%3D%20ColorRng.Interior.ColorIndex%20Then%0ABambo%20%3D%20True%0AExit%20For%0AEnd%20If%0ANext%20CF1%0ACF2%20%3D%200%0ACF3%20%3D%200%0AIf%20Bambo%20%3D%20True%20Then%0AFor%20Each%20CFCELL%20In%20CellsRange%0Adbw%20%3D%20CFCELL.FormatConditions(CF1).Formula1%0Adbw%20%3D%20Application.ConvertFormula(dbw%2C%20xlA1%2C%20xlR1C1)%0Adbw%20%3D%20Application.ConvertFormula(dbw%2C%20xlR1C1%2C%20xlA1%2C%20%2C%20ActiveCell.Resize(CellsRange.Rows.Count%2C%20CellsRange.Columns.Count).Cells(CF3%20%2B%201))%0AIf%20Evaluate(dbw)%20%3D%20True%20Then%20CF2%20%3D%20CF2%20%2B%201%0ACF3%20%3D%20CF3%20%2B%201%0ANext%20CFCELL%0AElse%0ACOUNTConditionColorCells%20%3D%20%22NO-COLOR%22%0AExit%20Function%0AEnd%20If%0ACOUNTConditionColorCells%20%3D%20CF2%0AEnd%20Function%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ethese%20solutions%20were%20provided%20to%20the%20similar%20question%20asked%20by%20other%20Excel%20users%20and%20worked%20for%20them.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20more%20detail%20here%20is%20the%26nbsp%3B%3CA%20title%3D%22Microsoft%20Tech%20Community%20Answer%20by%20Jamil%20Mohammad%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FMacros-and-VBA%2Fsum-by-color-when-colors-are-set-by-conditional-formatting%2Fm-p%2F36495%23M15%22%20target%3D%22_blank%22%3Elink%20for%20that%20answer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-134076%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-134076%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20the%20solution%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20474px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F25102i95ED48D0473F4432%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22ColorFunction.GIF%22%20title%3D%22ColorFunction.GIF%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1617055258%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%20id%3D%22toc-hId-1702950592%22%3E%3CSPAN%20class%3D%22mw-headline%22%3EReference%3C%2FSPAN%3E%3C%2FH2%3E%0A%3CP%3E%3CSPAN%20class%3D%22mw-headline%22%3E%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F1155-excel-count-sum-cells-by-color.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EHow%20to%20count%20and%20sum%20cells%20based%20on%20background%20color%20in%20Excel%3F%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-134039%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-134039%22%20slang%3D%22en-US%22%3EHi%2C%20this%20kind%20of%20question%20is%20better%20communicated%20with%20a%20document%2C%20stating%20the%20problems%20in%20short%20form%20in%20the%20worksheet.%20Thanks%20for%20sharing...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-417004%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-417004%22%20slang%3D%22en-US%22%3EYou%20are%20most%20welcome.%20%3CBR%20%2F%3E%3CBR%20%2F%3EYes.%20you%20can%20simply%20add%20another%20condition%20that%20cell%20%3D%22%22%20then%20select%20%22No%20color%22%20in%20the%20fill%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-418469%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-418469%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20want%20to%20count%20the%20number%20of%20yellow%20boxes%20as%20in%20the%20file%20below%20and%20output%20in%20the%20column%20%22count%20color%20yellow%22%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you%20so%20much!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-421844%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-421844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20found%20this%20code%20to%20be%20used%20in%20my%20file%20but%20I%20want%20to%20switch%20from%20%22Sub%22%20to%20%22Function%22.%20That%20is%2C%20I%20want%20the%20result%20of%20the%20yellow%20box%20displayed%20in%20the%20column%20count%20color%20yellow.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EHelp%20me!!%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you%20very%20much!!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22line%20number1%20index0%20alt2%22%3ESub%20DisplayFormatCount()%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number2%20index1%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B'Updateby20150305%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number3%20index2%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BDim%20Rng%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EAs%20Range%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number4%20index3%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BDim%20CountRange%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EAs%20Range%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number5%20index4%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BDim%20ColorRange%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EAs%20Range%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number6%20index5%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BDim%20xBackColor%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EAs%20Long%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number7%20index6%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BDim%20xFontColor%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EAs%20Long%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number8%20index7%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BOn%20Error%20Resume%20Next%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number9%20index8%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BxTitleId%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%22KutoolsforExcel%22%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number10%20index9%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BSet%20CountRange%20%3D%20Application.Selection%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number11%20index10%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BSet%20CountRange%20%3D%20Application.InputBox(%22Count%20Range%20%3A%22%2C%20xTitleId%2C%20CountRange.Address%2C%20Type%3A%20%3D%20%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number12%20index11%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BSet%20ColorRange%20%3D%20Application.InputBox(%22Color%20Range(single%20cell)%3A%22%2C%20xTitleId%2C%20Type%3A%20%3D%20%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number13%20index12%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BSet%20ColorRange%20%3D%20ColorRange.Range(%22A1%22)%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number14%20index13%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BxReturn%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%200%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number15%20index14%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BFor%20Each%20Rng%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EIn%20CountRange%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number16%20index15%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bqqq%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%20Rng.Value%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number17%20index16%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bxxx%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D%20Rng.DisplayFormat.Interior.Color%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number18%20index17%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIf%20Rng.DisplayFormat.Interior.Color%20%3D%20ColorRange.DisplayFormat.Interior.Color%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EThen%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number19%20index18%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BxBackColor%26nbsp%3B%26nbsp%3B%20%3D%20xBackColor%20%2B%201%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number20%20index19%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BEnd%20If%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number21%20index20%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIf%20Rng.DisplayFormat.Font.Color%20%3D%20ColorRange.DisplayFormat.Font.Color%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EThen%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number22%20index21%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BxFontColor%20%3D%20xFontColor%20%2B%201%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number23%20index22%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BEnd%20If%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number24%20index23%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BNext%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number25%20index24%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BMsgBox%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%22BackColor%20is%20%22%20%26amp%3B%20xBackColor%20%26amp%3B%20Chr(10)%20%26amp%3B%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%22FontColor%20is%20%22%20%26amp%3B%20xFontColor%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number26%20index25%20alt1%22%3EEnd%20Sub%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-439642%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-439642%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20there!%26nbsp%3B%20I%20tried%20adding%20the%20formula%20E%2411%3D%22%22%20and%20format%20is%20blank%20but%20then%20the%20COUNTConditionColorCells%20formula%20returns%20%23Value!%26nbsp%3B%20Any%20idea%20what%20I'm%20doing%20wrong%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-446478%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-446478%22%20slang%3D%22en-US%22%3Etry%20to%20see%20if%20you%20have%20already%20a%20CF%20set%20for%20that%20cell%20with%20a%20different%20formula.%20then%20it%20will%20work.%20so%20you%20will%20need%20to%20use%20a%20workaround.%20use%20countblank%20function%20instead.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-446487%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-446487%22%20slang%3D%22en-US%22%3EHi%20hiepkt12%20%3CBR%20%2F%3E%3CBR%20%2F%3Eyou%20have%20posted%20the%20same%20question%20in%20another%20thread%20as%20well.%20I%20have%20replied%20to%20you%20in%20there.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455790%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455790%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHi%20Jamil%2C%3CBR%20%2F%3EThank%20you.%20Your%20replies%20to%20this%20post%20have%20helped%20me%20so%20much!%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20used%20your%20code%20and%20included%20Application.volatile.%20But%2C%20I%20can't%20get%20my%20spreadsheet%20to%20automatically%20calculate%20the%20number%20of%20coloured%20cells.%20It%20will%20only%20update%20the%20number%20of%20cells%20if%20I%20edit%20the%20cell%20in%20the%20formula%20bar.%20Is%20there%20a%20way%20I%20can%20set%20up%20a%20button%20on%20my%20spreadsheet%20that%20will%20force%20excel%20to%20calculate%20how%20many%20cells%20have%20been%20coloured%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455968%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455968%22%20slang%3D%22en-US%22%3EHi%20Sonia1640%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20check%20that%20your%20workbook%20setting%20calculation%20mode%20is%20%22Automatic%22%20%3CBR%20%2F%3E%3CBR%20%2F%3Eon%20the%20Formula%20Tab%20click%20on%20%22Calculation%20Option%22%20and%20from%20the%20dropdown%20plz%20make%20sure%20%22Automatic%22%20is%20selected.%20If%20it%20is%20Manual%20then%20change%20it%20to%20Automatic.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-457443%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-457443%22%20slang%3D%22en-US%22%3EHi%20Jamil%2C%3CBR%20%2F%3EThe%20workbook%20is%20set%20to%20automatic.%20Do%20you%20have%20any%20other%20ideas%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-457861%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-457861%22%20slang%3D%22en-US%22%3EHi%20Sonia%2C%3CBR%20%2F%3E%3CBR%20%2F%3EPlz%20check%20if%20you%20have%20a%20personal%20excel%20file%20in%20the%20Excel%20start%20up%20folder.%20Also%2C%20check%20if%20you%20have%20any%20Excel%20Add-ins%20enabled.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20the%20above-mentioned%20did%20not%20work.%20then%20place%20this%20code%20below%20into%20sheet%20module%20not%20in%20a%20regular%20module.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20can%20change%20the%20range%20A2%3AZ100%20to%20the%20actual%20range%20where%20your%20formulas%20are%20placed.%3CBR%20%2F%3E%3CBR%20%2F%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%3CBR%20%2F%3E%20Me.Range(%22A2%3AZ100%22).Calculate%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477696%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477696%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3BHi%20Jamil%2C%20I%20thank%20you%20for%20your%20work%20and%20effort%20to%20help.%3CBR%20%2F%3EBut%2C%20it%20seems%20your%20code%20is%20not%20working%20here%20because%20I%20need%20to%20see%20conditional%20formatting%20changing%20colors%20at%20columns%20by%20the%20values%20of%20the%20cells%20and%20I%20need%20to%20count%20those%20color%20changes%20at%20rows%20direction%2C%20after%20that.%3CBR%20%2F%3EDo%20you%20think%20that%20it%20can%20be%20done%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-502542%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-502542%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20try%20your%20VBA%20formula%20for%20sum%20by%20conditional%20formatting%20color%2C%20however%20formula%20return%20%220%22%20result%20when%20data%20are%20in%20negative.%20I%20did%20conditionally%20formatted%20negative%20data%20by%20using%20ABS%20formula.%20I%20have%20staff%20attendance%20record%20which%20shows%20short%20hours%20by%20negative%20sign%20and%20excess%20hours%20by%20positive%20numbers.%20now%20I%20need%20to%20sum%20all%20short%20hours%20and%20excess%20hours%20first%20by%20highlighting%20through%20conditional%20format%20and%20then%20sum%20up%20those%20hours.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewould%20you%20please%20help%20me%20to%20achieve%20this.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-538414%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-538414%22%20slang%3D%22en-US%22%3EI%20did%20not%20see%20any%20conditional%20formatting%20in%20your%20file.%3CBR%20%2F%3E%3CBR%20%2F%3Eso%2C%20I%20did%20not%20understand%20what%20were%20you%20trying%20to%20achieve.%20If%20you%20want%20to%20sum%20the%20total%20of%20cells%20that%20are%20negative%20then%20you%20can%20use%20lets%20say%20for%20Column%20A%20as%20an%20example%20formula%20is%20%3DSUMIF(A2%3AA40%2C%22%26lt%3B0%22)%20this%20will%20sum%20all%20values%20in%20cells%20that%20are%20negative.%20but%20if%20you%20want%20to%20count%20cells%20that%20have%20negative%20values%20then%20you%20can%20use%20%3DCOUNTIF(A2%3AA40%2C%22%26lt%3B0%22)%3CBR%20%2F%3E%3CBR%20%2F%3Eand%20to%20sum%20the%20positive%20numbers%20%3DSUMIF(A2%3AA40%2C%22%26gt%3B0%22)%20and%20to%20count%20the%20positive%20numbers%20%3DCOUNTIF(A2%3AA40%2C%22%26gt%3B0%22)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-539079%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-539079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Jamil%2C%3C%2FP%3E%3CP%3EThank%20you%20for%20response.%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20I%20did%20not%20make%20clear%20what%20I'm%20looking%20for.%20I%20do%20not%20want%20all%20negative%20to%20be%20sum%20only%20those%20cell%20value%20which%20is%20greater%20than%20-1.5%20and%20less%20than%20-10.00%20should%20be%20colored%20and%20sum%20and%20similarly%20all%20the%20cell%20value%20greater%20than%20positive%201.5%20are%20to%20colored%20and%20sum.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20done%20conditional%20formatting%20in%20attached%20file%20and%20try%20to%20apply%20your%20formula%20but%20its%20not%20working.%20Please%20help%20me%20out%20with%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-539082%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-539082%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330303%22%20target%3D%22_blank%22%3E%40nashsulanki%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20cell%20CL2%20put%20this%20formula%20and%20drag%20down%26nbsp%3B%3DSUMPRODUCT((ABS(BP2%3ACK2)%26gt%3B1.5)*(ABS(BP2%3ACK2%26lt%3B10)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20in%20CM2%20put%20this%20formula%20and%20drag%20down%26nbsp%3B%3DSUMPRODUCT((--(BP2%3ACK2%26gt%3B1.5)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-539233%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-539233%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20this%20formula%20it%20is%20counting%20colored%20cell.%20For%20example%20when%20I%20copied%20the%20formula%20to%20CL2%20result%20showing%2022%20which%20is%20count%20of%20color%20cell%20(or%20condition%20of%20greater%20than%20-1.5%20and%20less%20than%20-10).%20I%20want%20sum%20of%20all%20color%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-570074%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-570074%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20so%20VERY%20impressed%20with%20your%20expertise%20in%20MS%20Excel.%20I'm%20trying%20to%20do%20this%20very%20same%20task%20of%20counting%20cells%20based%20on%20CF%20color.%20I've%20attempted%20your%20code%20but%20get%20%22NO-COLOR%22%20as%20the%20output.%20I've%20attached%20a%20dummy%20file%20so%20you%20can%20see%20what's%20going%20on.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20thing%20I%20can%20figure%20is%20that%20it's%20a%20different%20version%20of%20excel%20or%20I%20have%20a%20setting%20blocked%20somewhere.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20your%20kind%20consideration%20and%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-570357%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-570357%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330303%22%20target%3D%22_blank%22%3E%40nashsulanki%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20you%20want%20to%20sum%20instead%20of%20count%20then%20use%26nbsp%3B%3DSUMPRODUCT((ABS(BP2%3ACK2)%26gt%3B1.5)*(ABS(BP2%3ACK2%26lt%3B10))%2CBP2%3ACK2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-570377%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-570377%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342577%22%20target%3D%22_blank%22%3E%40DarylMeese%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20for%20your%20kind%20words.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20looked%20at%20the%20file%20you%20attached.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20read%20my%20earlier%20posts%2C%20I%20have%20mentioned%20to%20other%20users%20with%20similar%20issues%20as%20stated%20below%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EYour%20CF%20range%20does%20not%20use%20only%20formula%2C%20for%20UDF%20to%20work%2C%20CF%20needs%20to%20be%20set%20up%20only%20using%20formula%20and%20not%20built-in%20options%20given%20for%20example%20you%20use%20the%20option%20of%20%22Format%20cells%20that%20contain%22%3C%2FLI%3E%0A%3CLI%3EUDF%20range%20uses%20the%20whole%20A%20to%20M%20columns%2C%20whereas%20your%20CF%20ranges%20are%20completely%20different.%20for%20example%26nbsp%3Bsome%20of%20them%20starts%20from%20row%20one%20and%20some%20in%20row%202%20%2C%26nbsp%3B%20%26nbsp%3Banother%20CF%20uses%20double%20column%20applied%20range%26nbsp%3B%24H%242%3A%24H%2431%2C%24J%242%3A%24J%2431%26nbsp%3B%3C%2FLI%3E%0A%3CLI%3EFor%20UDF%20to%20work%2C%20the%20range%20in%20UDF%20and%20ranges%20in%20CF%20must%20match%20and%20the%20CFs%20needs%20to%20be%20set%20using%20only%20formula.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EHope%20this%20helps.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-580537%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-580537%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Mr%20Jamil%3C%2FP%3E%3CP%3EI%20would%20like%20to%20thank%20you%20for%20this%20formula.%20It%20works%20great!%20However%2C%20I%20have%20a%20query.%20Please%20see%20attached%20file.%20I%20have%20a%20set%20of%20KPI%E2%80%99s%20for%20disciplines%20to%20meet.%20When%20I%20use%20your%20formula%20to%20count%2C%20%E2%80%98No%20of%20disciplines%20met%20by%20KPI%E2%80%99%2C%20it%20works.%20However%2C%20when%20I%20used%20your%20formula%20to%20count%2C%20%E2%80%98No%20of%20KPI's%20met%20by%20Discipline%E2%80%99%2C%20it%20doesn%E2%80%99t%20work.%20May%20I%20know%20what%20could%20be%20wrong%3F%20Your%20help%20is%20greatly%20appreciated%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-614953%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-614953%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Jamil%2C%3C%2FP%3E%3CP%3EThanks%20for%20sharing%20the%20code%20for%20counting%20colored%20cells%20in%20a%20conditionally%20formatted%20sheet.%20Just%20a%20quick%20question%2C%20%22Can%20you%20please%20help%20me%20alter%20the%20code%20to%20make%20it%20count%20the%20colored%20cells%20in%20a%20specific%20row.%20This%20current%20code%20is%20default%20for%20range%20starting%20from%20cell%20A3.%20Say%20suppose%20I%20want%20to%20count%20the%20colored%20cells%20for%20range%20A4%20to%20G4%20in%20your%20sheet%20'COUNTBYCOLOR.xls%2C%20it%20does%20not%20give%20me%20correct%20result.%22%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-621124%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-621124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F343700%22%20target%3D%22_blank%22%3E%40emy_77%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20is%20because%20your%20CF%20is%20set%26nbsp%3BC3%3AG3%20and%20not%26nbsp%3BC3%3AC5%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eso%20only%26nbsp%3BC3%3AG3%20works%20because%20your%20CF%20has%20this%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-621139%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-621139%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F344871%22%20target%3D%22_blank%22%3E%40sudeep_batabyal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20you%20want%20to%20count%20by%20row%20then%20your%20conditional%20formatting%20applied%20range%20should%20be%20also%20by%20that%20row%20and%20now%20the%20entire%20range.%26nbsp%3B%20so%20if%20you%20want%20it%20to%20work%20on%26nbsp%3B%3CSPAN%3EA4%20to%20G4%26nbsp%3B%20then%20set%20up%20the%20conditional%20formatting%20range%20to%20be%20applied%20only%20on%26nbsp%3BA4%3AG4%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679978%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20There%3C%2FP%3E%3CP%3EI%20know%20very%20little%20about%20VBA's.%3C%2FP%3E%3CP%3EI've%20got%20a%20very%20large%20s%2Fsheet%20with%20data%20that%20I've%20just%20inherited.%20I%20need%20to%20add%20this%20months%20data%20to%20the%20master%20sheet%20(hystoric)%20and%20I'm%20trying%20to%20make%20the%20process%20easier%20and%20less%20time%20consuming%20(previously%20done%20manually).%20It's%20got%20columns%20with%20Customer%20Name%2C%20Address%2C%20Territory%2C%20Serial%20Number%2C%20Device%20Name%2C%20etc%20(columns%20A%20to%20O)%20and%20then%20approx%206900%20rows%20of%20data.%20In%20my%20Serial%20Number%20column%20I've%20got%20a%20conditional%20format%20(column%20H)%20(format%20values%20where%20this%20formula%20is%20true%20%3Dcountif(if%20serial%20number%20is%20different%20to%20last%20month)%20-%20fill%20-%20red)%20and%20then%20I've%20got%20a%20conditional%20format%26nbsp%3Bin%20column%20K%20(format%20values%20where%20device%20name%20is%20different%20to%20last%20month%20-%20fill%20-%20blue).%20I've%20also%20got%20subtotals%20-%20count%20per%20territory%20number.%3C%2FP%3E%3CP%3EWhat%20I'm%20trying%20to%20achieve%20is%20to%20count%20all%20the%20non-coloured%20Serial%20Numbers%20(cells%20in%20H)%20and%20all%20the%20change%20in%20device%20name%20(non-coloured%20cells%20in%20K%20(less%20non-coloured%20cells%20in%20H)).%3C%2FP%3E%3CP%3EI've%20inserted%20your%20UDF%20in%20VBA.%20In%20H373%20(first%20change%20in%20territory)%20I've%20typed%20%3DCOUNTConditionColorCells%20and%20hit%20Enter%20and%20get%20the%20%22%23Value!%22%20error.%20Not%20sure%20what%20I'm%20doing%20wrong%2C%20please%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-684905%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-684905%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20this%20formula%20and%20I%20keep%20getting%200's.%20I'm%20not%20sure%20what%20I'm%20doing%20wrong%20and%20I'm%20wondering%20if%20you%20can%20help.%20See%20my%20data.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-706150%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-706150%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F357335%22%20target%3D%22_blank%22%3E%40CeliaT%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20you%20upload%20a%20dummy%20sample%20file%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-706157%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-706157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F358499%22%20target%3D%22_blank%22%3E%40ekimble83%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20read%20my%20earlier%20posts%2C%20you%20will%20see%20that%20I%20mentioned%20UDF%20only%20works%20if%20CF%20is%20set%20using%20formula%20not%20build%20it%20conditions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20changed%20the%20CF%20in%20the%20attached%20file%20as%20an%20example%20and%20see%20it%20works.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725272%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725272%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Jamil%2C%20trying%20to%20use%20the%20count%20feature%20on%20conditional%20formatted%20cells%2C%20but%20the%20count%20is%20not%20accurate.%20My%20range%20of%20cells%20are%20restricted%20to%201%20row%20at%20a%20time%20of%20cell%20highlighted%20in%20blue%2C%20but%20it%20appears%20to%20give%20the%20same%20result%2C%20regardless%20of%20the%20range%20provided.%20The%20spreadsheet%20has%20been%20attached.%20Any%20thought%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-732761%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-732761%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20similar%20question%2C%20however%2C%20when%20I%20applied%20the%20codes%20it%20only%20counts%20the%20cell%20by%20column%2C%20which%20I%20also%20need%20it%20to%20be%20counted%20by%20row.%20I%20wonder%20if%20it%20is%20because%20each%20column%20has%20a%20different%20condition%20formatting%20criteria.%26nbsp%3B%20Could%20you%20please%20help%3F%20Please%20see%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-732766%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-732766%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20similar%20question%2C%20however%2C%20when%20I%20applied%20the%20codes%20it%20only%20counts%20the%20cell%20by%20column%2C%20which%20I%20also%20need%20it%20to%20be%20counted%20by%20row.%20I%20wonder%20if%20it%20is%20because%20each%20column%20has%20a%20different%20condition%20formatting%20criteria.%26nbsp%3B%20Could%20you%20please%20help%3F%20Please%20see%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-732882%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-732882%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367722%22%20target%3D%22_blank%22%3E%40mwmcgla%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20CF%20range%20was%20vertical%20while%20the%20formula%20range%20was%20horizontal.%26nbsp%3B%20so%20i%20changed%20your%20CF%20range%20to%20horizontal%20and%20it%20works%20now.%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-732884%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-732884%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369494%22%20target%3D%22_blank%22%3E%40vc337%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%2C%26nbsp%3B%20please%20read%20my%20earlier%20posts.%26nbsp%3B%20the%20UDF%20do%20not%20work%20if%20the%20CF%20is%20set%20using%20built-in%20conditions.%26nbsp%3B%20UDF%20works%20only%20when%20you%20set%20conditions%20with%20formula.%20%22Use%20a%20formula%20to%20determine%20which%20cell%20to%20format%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20you%20set%20your%20CF%20using%20formula%2C%20then%20it%20works.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-741609%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-741609%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20could%20you%20please%20check%20my%20file%2C%20i%20can't%20make%20it%20work%2C%20it%20gives%20me%20the%20error%3A%3CBR%20%2F%3E%23VALOR!%3C%2FP%3E%3CP%3EEnglish%20is%20not%20my%20first%20language%2C%20so%20i%20tried%20to%20make%20it%20work%20reading%20the%20posts%20here.%20Thanks%20if%20you%20can%20take%20a%20look%20into%20my%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759538%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759538%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20I%20try%20your%20code%20and%26nbsp%3B%20i%20still%20cant%20count%20color%20in%20my%20workbook.%20keep%20saying%20no%20colour%2C%20eventough%20its%20already%20the%20same%20color%3C%2FP%3E%3CP%3Ecan%20you%20take%20a%20look%20at%20my%20file%3F%20really%20appreciate%20it%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20it%20because%20I%20am%20using%20excel%202013%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-763417%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763417%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3Eyour%20conditional%20formatting%20range%20and%20the%20range%20used%20inside%20the%20UDF%20are%20not%20the%20same%2C%20that%20is%20why%20it%20does%20not%20work.%20please%20read%20my%20earlier%20comments%20to%20other%20users%20with%20similar%20issue.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765044%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765044%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Jamil%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20using%20your%20UDF%20for%20counting%20conditional%20cells%20and%20it%20has%20been%20working%20great.%20I%20was%20wondering%20if%20there%20is%20a%20way%20to%20modify%20the%20code%20so%20that%20when%20I%20filter%20my%20data%20in%20a%20table%2C%20the%20totals%20will%20update%20automatically%20without%20counting%20the%20hidden%20rows.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-777786%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-777786%22%20slang%3D%22en-US%22%3EHi%20Robin%2C%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20is%20maybe%20possible%20to%20do%20that%2C%20but%20I%20do%20not%20have%20time%20to%20write%20the%20code.%20I%20suggest%20you%20open%20a%20new%20question%20in%20the%20forum.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-833051%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-833051%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20rather%20large%20excel%20file%20with%20480%20columns%20and%201451%20rows.%26nbsp%3B%20I%20currently%20have%20conditional%20formatting%20set%20up%20to%20find%20-%20and%20color%20-%20the%20largest%20value%20in%20each%20row.%26nbsp%3B%20What%20I%20need%20to%20know%20is%20the%20count%20of%20row%20maximums%20in%20each%20column.%26nbsp%3B%20I%20see%20that%20your%20solution%20only%20works%20when%20the%20count%20function%20and%20conditional%20formatting%20are%20applied%20to%20the%20same%20range.%26nbsp%3B%20I%20already%20know%20how%20many%20I%20have%20per%20row%20-%201.%26nbsp%3B%20Is%20there%20any%20way%20to%20count%20conditional%20formatting%20when%20the%20ranges%20don't%20match%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-836897%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-836897%22%20slang%3D%22en-US%22%3EI%20could%20not%20understand%20your%20question.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20are%20trying%20to%20count%20number%20of%20cells%20when%20condition%20is%20not%20matched%2C%20then%20you%20simply%20sort%20your%20data%20by%20color%20of%20%22none%22%20and%20then%20count.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-839819%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-839819%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%2C%20I'm%20trying%20to%20count%20when%20the%20condition%20is%20met%2C%20however%2C%20my%20conditional%20formatting%20runs%20across%20rows.%26nbsp%3B%20I%20need%20to%20count%20instances%20when%20conditional%20formatting%20is%20matched%20within%20columns.%26nbsp%3B%20As%20you%20had%20previously%20mentioned%20to%20someone%20else%2C%20your%20tool%20for%20counting%20conditional%20formatting%20only%20works%20when%20the%20range%20and%20the%20area%20being%20counted%20match.%26nbsp%3B%20My%20question%20is%3A%20is%20there%20a%20workaround%20for%20when%20the%20conditional%20formattting%20range%20and%20the%20range%20being%20summed%20do%20not%20match%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFYI%20the%20same%20condition%20is%20being%20tested%20for%20within%20each%20row%20for%20each%20row%20within%20the%20summed%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-839821%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-839821%22%20slang%3D%22en-US%22%3E%3CP%3EI%20know%20it%20is%20possible%20to%20sort%2Ffilter%20via%20color%20and%20I%20could%20do%20that%20-%20and%20manually%20count%20-%20for%20each%20of%20my%20480%20columns%3B%20but%20since%20my%20data%20will%20change%20frequently%20over%20the%20course%20of%20the%20project%2C%20I'd%20prefer%20not%20to%20manually%20count%20480%20columns%20dozens%2C%20possibly%20hundreds%2C%20of%20times.%20I%20would%20much%20prefer%20to%20have%20a%20readout%20listed%20for%20each%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842484%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842484%22%20slang%3D%22en-US%22%3EHi%20At%20my%20wits%20end%20on%20how%20to%20do%20this.%20I%20have%20a%20spreadsheet%20that%20I%20use%20to%20monitor%20a%20football%20pool.%20I%20have%208%20columns%20conditionally%20formatted%20to%20show%20highest%20value%2C%20now%20I'm%20trying%20to%20find%20a%20way%20to%20count%20any%20formatted%20high%20values%20in%20the%20rows%20that%20cross%20these%20columns.%20I've%20worked%20through%20just%20about%20everything%20previously%20mentioned%20in%20this%20thread%20and%20still%20cannot%20come%20up%20with%20the%20formula%20needed%20to%20count%20the%20high%20values.%20Any%20help%20would%20be%20hugely%20appreciated.%20I've%20attached%20a%20portion%20of%20the%20spreadsheet%20I'm%20working%20on.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842586%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842586%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3EHi%20Jamil%20I'm%20at%20my%20wits%20end%20on%20how%20to%20do%20this.%20I%20have%20a%20spreadsheet%20that%20I%20use%20to%20monitor%20a%20football%20pool.%20I%20have%208%20columns%20conditionally%20formatted%20to%20show%20highest%20value%2C%20now%20I'm%20trying%20to%20find%20a%20way%20to%20count%20any%20formatted%20high%20values%20in%20the%20rows%20that%20cross%20these%20columns.%20I've%20worked%20through%20just%20about%20everything%20previously%20mentioned%20in%20this%20thread%20and%20still%20cannot%20come%20up%20with%20the%20formula%20needed%20to%20count%20the%20high%20values.%20Any%20help%20would%20be%20hugely%20appreciated.%20I've%20attached%20a%20portion%20of%20the%20spreadsheet%20I'm%20working%20on.%20Edit%3A%20After%20further%20researching%20this%20issue%2C%20and%20reading%20the%20rest%20of%20the%20posts%20in%20this%20conversation%2C%20as%20I%20should%20have%2C%20it%20appears%20that%20what%20I'm%20trying%20to%20do%20isn't%20possible.%20Hard%20to%20believe%20that%20something%20as%20simple%20as%20counting%20colored%20cells%20across%20rows%20that%20are%20formatted%20by%20conditional%20formatting%20in%20columns%2C%20can't%20be%20done.%20I've%20looked%20for%20other%20options%20to%20do%20this%20and%20can't%20find%20anything%20that%20suits%20me.%20Is%20there%20a%20way%20this%20can%20be%20done%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-862622%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20%22Conditional%20formatted%22%20cells%20by%20FONT%20COLOR%20in%20Excel%202016%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862622%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi!%26nbsp%3B%20I%20came%20across%20to%20%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FHow-to-count-and-sum-quot-Condtional-formatting-quot-cells-by%2Fm-p%2F116110%22%20target%3D%22_self%22%3Ethis%3C%2FA%3E%3C%2FSTRONG%3E%26nbsp%3Bthread%20coz%20i%20was%20looking%20for%20an%20answer%20to%20my%20problem.%26nbsp%3B%20And%20found%20that%20you%20posted%20one%20for%20%22%3CSTRONG%3E%3CSPAN%20class%3D%22lia-link-navigation%20lia-link-disabled%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fsum-by-color-when-colors-are-set-by-conditional-formatting%2Fm-p%2F36495%22%20target%3D%22_self%22%3Esum%20by%20color%20when%20colors%20are%20set%20by%20conditional%20formatting%3C%2FA%3E%22%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EHowever%2C%20what%20I%20want%20is%20%3CSTRONG%3Eto%20count%20all%20cells%20with%20red%20font%20color%20in%20a%20range%3C%2FSTRONG%3E%20but%20the%20%3CSTRONG%3EFONT%20COLOR%3C%2FSTRONG%3E%20was%20%3CSTRONG%3Ebased%20on%20the%20conditional%20formatting%3C%2FSTRONG%3E.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%20%3CSTRONG%3Erange%20is%20A1%3AA1000%26nbsp%3B%3C%2FSTRONG%3E%20then%20the%20formatting%20I%20made%20was%20to%20turn%20%3CSTRONG%3EDUPLICATE%20ENTRIES%3C%2FSTRONG%3E%20into%20a%20%3CSTRONG%3ERED%20FONT%20COLOR%3C%2FSTRONG%3E.%26nbsp%3B%20regardless%20of%20the%20text%20content.%26nbsp%3B%20if%20apple%20and%20banana%20were%20duplicated%20twice.%26nbsp%3B%20the%20result%20should%20give%20me%20%3CSTRONG%3ENOT%202%20BUT%204%3C%2FSTRONG%3E.%26nbsp%3B%20Please%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-877619%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20%22Conditional%20formatted%22%20cells%20by%20FONT%20COLOR%20in%20Excel%202016%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-877619%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%2C%3C%2FP%3E%3CP%3ECould%20you%20please%20look%20into%20a%20file%20attached%3F%20I%20am%20trying%20to%20count%20CF%20colored%20cells%20in%20%22SJA%20racks%22%20tab.%26nbsp%3B%3C%2FP%3E%3CP%3EGetting%20%220%22%20all%20the%20time.%20I%20ahve%20tried%26nbsp%3BGetColorCount%20and%26nbsp%3BCOUNTConditionColorCells%20%3D%20same%20result%20%220%22.%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20am%20I%20missing%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20looking%20into%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880948%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20%22Conditional%20formatted%22%20cells%20by%20FONT%20COLOR%20in%20Excel%202016%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880948%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Alex%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20need%20to%20overkill%20it%20with%20UDF.%20you%20can%20use%20built-in%20Excel%20formula%20to%20get%20what%20you%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esimply%20put%20this%20formula%20in%20M6%20cell%20of%20SJA%20racks%20sheet%20and%20copy%20it%20down%20up%20to%20cell%20M51.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esee%20also%20in%20the%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880949%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20%22Conditional%20formatted%22%20cells%20by%20FONT%20COLOR%20in%20Excel%202016%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880949%22%20slang%3D%22en-US%22%3EHi%20Pau_Me%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20already%20answered%20to%20this%20question%20in%20another%20forum.%20Please%20get%20the%20code%20or%20example%20workbook%20from%20here%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fsum-by-color-when-colors-are-set-by-conditional-formatting%2Fm-p%2F174487%2Fhighlight%2Ftrue%23M8272%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fsum-by-color-when-colors-are-set-by-conditional-formatting%2Fm-p%2F174487%2Fhighlight%2Ftrue%23M8272%3C%2FA%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881000%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881000%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405235%22%20target%3D%22_blank%22%3E%40bowler813%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20do%20not%20need%20UDF%20for%20this%2C%20you%20can%20accomplish%20the%20same%20with%20this%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((--(B3%3DMAX(%24B%243%3A%24B%2412))%2B(C3%3DMAX(%24C%243%3A%24C%2412))%2B(D3%3DMAX(%24D%243%3A%24D%2412))%2B(E3%3DMAX(%24E%243%3A%24E%2412))%2B(F3%3DMAX(%24F%243%3A%24F%2412))%2B(G3%3DMAX(%24G%243%3A%24G%2412))%2B(H3%3DMAX(%24H%243%3A%24H%2412))%2B(I3%3DMAX(%24I%243%3A%24I%2412))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ealso%20see%20it%20in%20the%20attached%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881501%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881501%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Jamil%2C%20this%20formula%20works%20great%20except%20when%20all%20the%20cells%20in%20an%20array%20are%20blank.%20If%20all%20the%20cells%20in%20an%20array%20are%20blank%20then%20they%20are%20all%20equal%2C%20so%20it%20counts%20each%20as%20the%20%22max%22.%20I've%20been%20searching%20for%20the%20possibility%20of%20using%20an%20IF%20to%20check%20if%20cell%20is%20blank%2C%20but%20not%20finding%20anything%20that%20suits%20my%20spreadsheet.%20How%20can%20I%20get%20around%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%20for%20your%20assistance%20in%20this.%20I'm%20not%20a%20strong%20user%20of%20Excel%20so%20need%20some%20help%20from%20time%20to%20time.%20And%20your%20expertise%20in%20invaluable!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJeff%20Cupps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888414%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888414%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20read%20all%20your%20posts%20and%20appreciate%20all%20the%20comments%20from%20you%20and%20the%20other%20users.%3C%2FP%3E%3CP%3EBut%20(there%20is%20always%20a%20but)%20I%20can't%20understand%2C%20why%20I%20am%20getting%20%23Value!%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20have%20a%20couple%20of%20questions%20for%20you%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E1.%20How%20I%20can%20create%20an%20applies%20to%20conditional%20format%20per%20row%20using%20the%20format%20painter%20button%3F%20I%20watch%20that%20in%20your%20video%2C%20but%20doesn't%20work%20for%20me%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EAt%20row%20five%20I%20created%20a%20conditional%20format%20%3DC5%3AL5%3C%2FP%3E%3CP%3EEach%20time%20I%20press%20enter%20(OK)%2C%20it%20cames%20to%26nbsp%3B%3D%24C%245%3A%24L%245%3C%2FP%3E%3CP%3EAnd%20if%20I%20use%20the%20format%20painter%20to%20copy%20to%20the%20remain%20rows%20it%20will%20be%20like%20this%20%3D%24C%246%3A%24L%2432%3C%2FP%3E%3CP%3EWhen%20should%20be%20like%3A%20C6%3AL6%2C%20then%20C7%3AL7%20...%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20The%20count%20conditional%20formatting%20cell%20per%20color%20isn't%20working%20(O5)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20support%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888611%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888611%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405235%22%20target%3D%22_blank%22%3E%40bowler813%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20revised%20the%20formula%20to%20take%20care%20of%20the%20blanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888629%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418600%22%20target%3D%22_blank%22%3E%40Oscar_PT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20I%20checked%20your%20file.%26nbsp%3B%20%26nbsp%3Bthe%20UDF%20will%20not%20work%20this%20sort%20of%20range.%20because%20your%20conditional%20range%20is%20not%20within%20the%20range.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20did%20not%20understand%20why%20your%20conditional%20formatting%20rule%20starts%20from%20C6%20while%20the%20data%20is%20from%20C5.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20put%20a%20formula%20in%20the%20attached%20file%20that%20shows%20how%20you%20can%20count%20the%20range%20of%20cells%20that%20meets%20the%20criteria%20within%20another%20range.%26nbsp%3B%20see%20if%20you%20can%20work%20that%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889009%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOnce%20again%20thank%20you%20Jamil!%20Your%20help%20has%20been%20invaluable.%20The%20changes%20you've%20made%20work%20perfectly.%20I've%20attached%20a%20copy%20of%20the%20spreadsheet%20end%20result%2C%20in%20case%20you%20were%20curious.%20Also%20for%20anyone%20who%20might%20find%20themselves%20with%20the%20same%20issues.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EMany%20thanks%20to%20you%20my%20friend!!%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EJeff%20Cupps%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893126%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893126%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20comments!%3CBR%20%2F%3EI%20will%20explain%20that%20file%3A%3C%2FP%3E%3CP%3E%26gt%3BIn%20a%20company%2C%20workers%20use%20the%20national%20lottery%20to%20play%20a%20game%20(like%20bingo).%3CBR%20%2F%3E%26gt%3BEach%20week%20the%20lottery%20has%202%20contests%20(C42%3AG73)%20and%20each%20contest%205%20numbers%3B%3C%2FP%3E%3CP%3E%26gt%3B%20The%20first%20worker%20C5%3AL32%20to%20complete%20all%20his%20number%20(per%20row)%20will%20win%20all%20the%20money%3B%3C%2FP%3E%3CP%3E%26gt%3BThere%20is%20a%20conditional%20format%20to%20paint%20as%20red%20(when)%20the%20worker%20hit%20the%20number%3B%3C%2FP%3E%3CP%3EThe%20person%20who%20is%20controlling%20this%20%E2%80%9Csocial%20enterprise%20game%E2%80%9D%20counts%20(cell%20by%20cell%2C%20without%20using%20a%20form%20or%20a%20macro)%20the%20number%20of%20whites%2C%20to%20know%20how%20many%20missing%20numbers%20each%20worker%20has.%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20replace%20the%20%E2%80%9Cmanual%20counting%E2%80%9D%20with%20your%20script%2C%20we%20wish%20to%20count%20the%20missing%20numbers%20(white%20or%20without%20conditional%20format)%20%3D%20like%20column%20M.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-977754%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-977754%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418600%22%20target%3D%22_blank%22%3E%40Oscar_PT%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20this%20formula%26nbsp%3B%3DSUMPRODUCT(1-(COUNTIFS(%24C%2442%3A%24G%2410010%2CC5%3AL5)%26gt%3B0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20see%20attached%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1023564%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1023564%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI've%20been%20trying%20to%20adapt%20your%20code%20to%20count%20cells%20in%20a%20range%20that%20have%20fonts%20that%20are%20conditionally%20formatted%20(bold-red%20or%20bold-green).%20The%20cells%20use%20a%20formula%20to%20decide%20the%20condition.%20I%20found%20this%20simple%20function%20code%20below%20but%20it%20looks%20for%20font%20color%20rather%20than%20conditional%20formatted%20font%20color.%20Any%20help%20you%20can%20provide%20would%20be%20awesome.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFunction%20CountColour(rng%20As%20Range%2C%20clr%20As%20Range)%3CBR%20%2F%3EApplication.Volatile%3CBR%20%2F%3EDim%20c%20As%20Range%3CBR%20%2F%3EFor%20Each%20c%20In%20rng%3CBR%20%2F%3EIf%20c.Font.Color%20%3D%20clr.Font.Color%20Then%3CBR%20%2F%3ECountColour%20%3D%20CountColour%20%2B%201%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ER%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1024877%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1024877%22%20slang%3D%22en-US%22%3Eplease%20see%20in%20my%20earlier%20posts%20and%20find%20the%20function%20which%20is%20for%20count%20based%20on%20the%20font%20color.%3CBR%20%2F%3Eonce%20you%20found%20it%2C%20then%20change%20the%20object%20property%20from%20font.color%20to%20font.bold%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1077611%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1077611%22%20slang%3D%22en-US%22%3E%3CP%3EHI%20Jamil.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20went%20through%20all%20the%20discussion%20here%20and%20yet%20I%20can%20make%20my%20excel%20sheet%20to%20work%20with%20this%20code%2C%20I%20just%20get%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20have%20a%20look%20at%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20attaching%20the%20macro-enable%20sheet%20and%20the%20one%20without%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20have%20a%20summary%20table%20of%20how%20many%20people%20have%20the%20training%20completed%2C%20about%20to%20expired%2C%20experied%2C%20and%20never%20been%20taken.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERGS.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1081547%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1081547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F495922%22%20target%3D%22_blank%22%3E%40AngelRodriguez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%26nbsp%3B%20no%20need%20for%20UDF.%20I%20have%20build%20formula%20to%20do%20the%20calculation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20word%20TODAY%20in%20the%20formulas%20are%20a%20named%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1091403%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1091403%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%2C%20it%20is%20working%20now%2C%20I%20don't%20quite%20understand%20what%20was%20the%20reason%20for%20this%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20thing%20is%20that%20I%20just%20noticed%20my%20color%20coded%20formating%20is%20not%20working%20as%20I%20expected%2C%20I%20set%20the%20cell%20date%20within%2045%20days%20from%20today%20to%20change%20to%20Orange%20color%20which%20are%20the%20trainings%20about%20to%20expired.%20and%20if%20the%20date%20in%20the%20cell%20is%20less%20or%20equal%20to%20today%C2%B4s%20date%20should%20turn%20into%20red%20but%20it%20is%20not%20working%2C%20it%20is%20happening%20to%20cells%20over%2045%20days%20I%20think.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20noticed%20that%20if%20the%20cell%20changed%20to%20orange%20is%20it%20not%20counting%20yet%20in%20the%20summary%2C%20can%20you%20have%20a%20look%20one%20more%20time%20and%20thanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%20happy%20new%20year%20from%20Argentina.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERGS.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1092279%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1092279%22%20slang%3D%22en-US%22%3EYou%20have%20set%20the%20Conditional%20formatting%20incorrectly.%20instead%20of%20putting%20TODAY()%2B45%20you%20have%20put%20TODAY()-45%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1180994%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1180994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Jamil%2C%20Your%20code%20is%20great.%20Unfortunately%20it%20didnt%20work%20like%20I%20intend%20it%20to%20do%2C%20I%20have%20used%20it%20in%20my%20gantt%20chart%20and%20I%20want%20to%20count%20the%20colored%20cells%20in%20a%20week.%20See%20my%20Dummy.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1190663%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190663%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F562736%22%20target%3D%22_blank%22%3E%40Clint00%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EI%20checked%20your%20file%2C%20none%20of%20the%20Functions%20which%20you%20used%20in%20the%20workbook%20is%20written%20by%20me.%20You%20should%20use%20the%20UDF%20which%20I%20wrote%2C%20you%20can%20find%20it%20in%20the%20same%20thread.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1300455%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1300455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%20code%2C%20but%20it%20only%20works%20for%20the%20first%20row.%20It%20doesn't%20work%20on%20the%20rest%20of%20the%20rows.%20What%20am%20I%20doing%20wrong%3F%20Please%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1308942%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1308942%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F618232%22%20target%3D%22_blank%22%3E%40Kalpesh64%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%26nbsp%3B%20%26nbsp%3BIt%20did%20not%20work%20because%2C%20the%20UDF%20is%20range%20sensitive.%26nbsp%3B%20the%20range%20set%20in%20Conditional%20formatting%20and%20the%20range%20used%20int%20he%20UDF%20must%20match.%26nbsp%3B%20%26nbsp%3BYour%20UDF%20ranges%20were%20looking%20to%20for%20each%20row%20and%20the%20CF%20was%20set%20for%20the%20entire%20range.%20I%20changed%20the%20CF%20range%20to%20single%20row%2C%20then%20used%20paint%20brush%20to%20create%20same%20CF%20for%20each%20row%20and%20then%20the%20UDF%20calculation%20started%20to%20work.%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20workbook%2C%20as%20well%20as%20a%20animated%20video%20on%20how%20did%20I%20do%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1312325%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1312325%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F623995%22%20target%3D%22_blank%22%3E%40khaled170%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20my%20answer%20which%20I%20replied%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F618232%22%20target%3D%22_blank%22%3EKalpesh64%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3Blink%20here%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fhow-to-count-and-sum-quot-condtional-formatting-quot-cells-by%2Fm-p%2F1308942%2Fhighlight%2Ftrue%23M58773%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fhow-to-count-and-sum-quot-condtional-formatting-quot-cells-by%2Fm-p%2F1308942%2Fhighlight%2Ftrue%23M58773%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20I%20have%20shown%20how%20you%20can%20do%20it%20per%20row.%26nbsp%3B%20file%20is%20attached%20there%20and%20also%20the%20GIF%20animated%20recorded%20video.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1312159%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20and%20sum%20%22Condtional%20formatting%22%20cells%20by%20color%20in%20Excel%202010%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1312159%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3BHow%20to%20use%20this%20function%20to%20row.%20I%20need%20to%20pull%20the%20formula%20and%20count%20the%20co%3Bored%20formatted%20cells%20in%20each%20row%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
@Jamil MohammadHi Jamil I'm at my wits end on how to do this. I have a spreadsheet that I use to monitor a football pool. I have 8 columns conditionally formatted to show highest value, now I'm trying to find a way to count any formatted high values in the rows that cross these columns. I've worked through just about everything previously mentioned in this thread and still cannot come up with the formula needed to count the high values. Any help would be hugely appreciated. I've attached a portion of the spreadsheet I'm working on. Edit: After further researching this issue, and reading the rest of the posts in this conversation, as I should have, it appears that what I'm trying to do isn't possible. Hard to believe that something as simple as counting colored cells across rows that are formatted by conditional formatting in columns, can't be done. I've looked for other options to do this and can't find anything that suits me. Is there a way this can be done?
Highlighted

@Jamil Mohammad 

 

Hi!  I came across to this thread coz i was looking for an answer to my problem.  And found that you posted one for "sum by color when colors are set by conditional formatting"

However, what I want is to count all cells with red font color in a range but the FONT COLOR was based on the conditional formatting

 

Example: range is A1:A1000  then the formatting I made was to turn DUPLICATE ENTRIES into a RED FONT COLOR.  regardless of the text content.  if apple and banana were duplicated twice.  the result should give me NOT 2 BUT 4.  Please help me.

 

Thanks in advance!

Highlighted

Hi @Jamil Mohammad,

Could you please look into a file attached? I am trying to count CF colored cells in "SJA racks" tab. 

Getting "0" all the time. I ahve tried GetColorCount and COUNTConditionColorCells = same result "0". 

What am I missing? 

Thanks in advance for looking into this.

Highlighted

Hi Alex,

 

No need to overkill it with UDF. you can use built-in Excel formula to get what you need.

 

simply put this formula in M6 cell of SJA racks sheet and copy it down up to cell M51.

 

see also in the attached

Highlighted
Hi Pau_Me

I have already answered to this question in another forum. Please get the code or example workbook from here

https://techcommunity.microsoft.com/t5/Excel/sum-by-color-when-colors-are-set-by-conditional-formatt...
Highlighted

@bowler813 

 

Hi, 

 

you do not need UDF for this, you can accomplish the same with this formula

 

=SUMPRODUCT((--(B3=MAX($B$3:$B$12))+(C3=MAX($C$3:$C$12))+(D3=MAX($D$3:$D$12))+(E3=MAX($E$3:$E$12))+(F3=MAX($F$3:$F$12))+(G3=MAX($G$3:$G$12))+(H3=MAX($H$3:$H$12))+(I3=MAX($I$3:$I$12))))

 

also see it in the attached workbook.

Highlighted

@Jamil Mohammad 

Hi Jamil, this formula works great except when all the cells in an array are blank. If all the cells in an array are blank then they are all equal, so it counts each as the "max". I've been searching for the possibility of using an IF to check if cell is blank, but not finding anything that suits my spreadsheet. How can I get around this?

 

Thank you again for your assistance in this. I'm not a strong user of Excel so need some help from time to time. And your expertise in invaluable!!

 

Jeff Cupps

Highlighted

@Jamil Mohammad 

I read all your posts and appreciate all the comments from you and the other users.

But (there is always a but) I can't understand, why I am getting #Value! error.

 

So I have a couple of questions for you:

1. How I can create an applies to conditional format per row using the format painter button? I watch that in your video, but doesn't work for me:

At row five I created a conditional format =C5:L5

Each time I press enter (OK), it cames to =$C$5:$L$5

And if I use the format painter to copy to the remain rows it will be like this =$C$6:$L$32

When should be like: C6:L6, then C7:L7 ...

How?

 

2. The count conditional formatting cell per color isn't working (O5) 

 

Thanks for your support

 

Highlighted

@bowler813 

 

Hi,

 

I have revised the formula to take care of the blanks.

 

please see attached.

Highlighted

@Oscar_PT 

 

Hi, I checked your file.   the UDF will not work this sort of range. because your conditional range is not within the range. 

 

I also did not understand why your conditional formatting rule starts from C6 while the data is from C5.

 

I have put a formula in the attached file that shows how you can count the range of cells that meets the criteria within another range.  see if you can work that out.

 

 

Highlighted

@Jamil Mohammad 

 

Once again thank you Jamil! Your help has been invaluable. The changes you've made work perfectly. I've attached a copy of the spreadsheet end result, in case you were curious. Also for anyone who might find themselves with the same issues.

Many thanks to you my friend!!

 

Jeff Cupps

Highlighted

@Jamil Mohammad 

 

Thanks for your comments!
I will explain that file:

>In a company, workers use the national lottery to play a game (like bingo).
>Each week the lottery has 2 contests (C42:G73) and each contest 5 numbers;

> The first worker C5:L32 to complete all his number (per row) will win all the money;

>There is a conditional format to paint as red (when) the worker hit the number;

The person who is controlling this “social enterprise game” counts (cell by cell, without using a form or a macro) the number of whites, to know how many missing numbers each worker has.

I am trying to replace the “manual counting” with your script, we wish to count the missing numbers (white or without conditional format) = like column M.


Thanks

 

Highlighted

@Oscar_PT

 

You can use this formula =SUMPRODUCT(1-(COUNTIFS($C$42:$G$10010,C5:L5)>0))

 

please see attached file. 

Highlighted

@Jamil Mohammad 

 

Hi,

I've been trying to adapt your code to count cells in a range that have fonts that are conditionally formatted (bold-red or bold-green). The cells use a formula to decide the condition. I found this simple function code below but it looks for font color rather than conditional formatted font color. Any help you can provide would be awesome. 

 

Function CountColour(rng As Range, clr As Range)
Application.Volatile
Dim c As Range
For Each c In rng
If c.Font.Color = clr.Font.Color Then
CountColour = CountColour + 1
End If
Next
End Function

 

Thanks,

Highlighted
please see in my earlier posts and find the function which is for count based on the font color.
once you found it, then change the object property from font.color to font.bold
Highlighted

HI Jamil.

 

I went through all the discussion here and yet I can make my excel sheet to work with this code, I just get 0.

 

Can you have a look at the data.

 

I am attaching the macro-enable sheet and the one without it.

 

I want to have a summary table of how many people have the training completed, about to expired, experied, and never been taken.

 

RGS.

Highlighted

@AngelRodriguez 

 

Hi,  no need for UDF. I have build formula to do the calculation.

 

the word TODAY in the formulas are a named range.

 

please see attached.

Highlighted

Hi @Jamil Mohammad.

 

Thanks for your help, it is working now, I don't quite understand what was the reason for this error.

 

Another thing is that I just noticed my color coded formating is not working as I expected, I set the cell date within 45 days from today to change to Orange color which are the trainings about to expired. and if the date in the cell is less or equal to today´s date should turn into red but it is not working, it is happening to cells over 45 days I think.

 

I also noticed that if the cell changed to orange is it not counting yet in the summary, can you have a look one more time and thanks in advance.

 

By the way happy new year from Argentina.

 

RGS.

 

Highlighted
You have set the Conditional formatting incorrectly. instead of putting TODAY()+45 you have put TODAY()-45
Highlighted

@Jamil Mohammad 

 

Hi Jamil, Your code is great. Unfortunately it didnt work like I intend it to do, I have used it in my gantt chart and I want to count the colored cells in a week. See my Dummy. Thanks

Highlighted
@Clint00

I checked your file, none of the Functions which you used in the workbook is written by me. You should use the UDF which I wrote, you can find it in the same thread.
Highlighted

@Jamil Mohammad 

 

I tried this code, but it only works for the first row. It doesn't work on the rest of the rows. What am I doing wrong? Please see attached.

Highlighted

@Kalpesh64 

 

Hi,   It did not work because, the UDF is range sensitive.  the range set in Conditional formatting and the range used int he UDF must match.   Your UDF ranges were looking to for each row and the CF was set for the entire range. I changed the CF range to single row, then used paint brush to create same CF for each row and then the UDF calculation started to work.

I have attached the workbook, as well as a animated video on how did I do it.

 

Highlighted

@Jamil Mohammad How to use this function to row. I need to pull the formula and count the co;ored formatted cells in each row

Highlighted

@khaled170 

 

Please see my answer which I replied to Kalpesh64   link here https://techcommunity.microsoft.com/t5/excel/how-to-count-and-sum-quot-condtional-formatting-quot-ce... 

 

There I have shown how you can do it per row.  file is attached there and also the GIF animated recorded video. 

Highlighted
Highlighted

@Jamil Mohammad Thank you but can you show me the code which you use to get this formula

Highlighted
Highlighted

@Jamil Mohammad Dear Mr. Jamil : I tried to use on my sheet but it give me #VALUE!, I attached the sheet. I used 2 formula ( in Cell AN2 and AN5 )for Formatting the cells . Please can you help me get the formula and show me the code.

Highlighted

@Jamil Mohammad I'm attaching a TEST file. Please direct your attention to cells: J5:J8, Column 'T', Column 'U', cells 'Z13:Z15' and 'AA13:AA15'. 

 

What I'm trying to achieve is for UDF to calculate/ COUNT the perspective colored cells in Column U accordingly; then report that number in Cells AA13, AA14 and AA15.  I then will copy/link that number or cell to my LEGEND located at Cell: R62 - R65.  Column U is the Color Indicator Column. Obviously the color will change based on the average time per student. I want to count those results appropriately to automatically convert a Weekly Percentage.  After applying your code, I still can't get the code to talk properly within the document.  Please Help!

Highlighted

@khaled170 

 

Perhaps, you did not read my earlier posts. I have mentioned that the range used in the UDF must be a mirror of the range in the conditional formatting.  I can see from your example file that you have applied multiple range in the single rule of conditional formatting  and also it is vertical by column and yet the UDF is using single range of single horizontal row. so the ranges do not match that is why you get the error.

Highlighted

@Lonnie White III

 

Hi,  I looked at your file.  there are many overlapping conditional formatting rules which you have set. for example the color yellow is overrulled by color red. so the true count of the yellow cells are distorted.

 

Perhaps, you are overkilling this by using this advanced udf for such calculation that can be done using built-in array formulas.  for example you count of yellow cells can be achieved using the =SUMPRODUCT((--(T4:T55<>""))*(T4:T55<J6)*(T4:T55>J7))   the first part of the array excludes blank cells and second part of array is exactly as per your conditional formatting and the third part of array is not to overrule the second part. as it is now in the conditional formatting.  try to calculate it using the built in formula as i have given example for one of the items in the list.

 

 

Highlighted

@Jamil Mohammad I already get the code running on my log sheet , but I am getting #VALUE! then If I select the formula and press Enter it will run and if I drag it below it will give correct answer only for 10 rows below and below this the formula showing #VALUE! then need from the formula in the last row which showing correct result to drag below and again it will give correct answer in the below 10 rows and the other row will show #VALUE!.

Is their any way to let the formula properly work for all the rows as I drag it below ?

Attached the file I am working with.

Highlighted

@Jamil Mohammad Thanks for the quick reply.  I implemented the formula you provide and it returned a number higher than what was expected. All I did was paste the formula in a blank cell and it returned "29" as a result. Let me know if I need to reorder my CF rules or try another strategy.  How do you feel about perhaps counting by color code?  Will this emit the same issue?

 

Thanks Jamil!!

 

LONNIE

Highlighted
Dear Mr. Jamil : I have a question regarding the update of the link of one formula in one workbook is not updating unless I will open the other work book.I try enable automatic link updates in Excel 2013 by selecting File, Options, Trust Center, Trust Center Settings, External Content, and under the section labeled Security settings for Workbook Links, select Enable automatic update for all Workbook Links, and then click OK. But nothing change. Please can you help.
Highlighted

@Lonnie White III 

 

In your excel file, you had a pre set value of 20 for yellow color (Below 30 minutes; more Login Time needed.)  I placed the formula =SUMPRODUCT((--(T4:T55<>""))*(T4:T55<J6)*(T4:T55>J7)) there and it returned 20.   similarly, for other conditions, you can specify what should be the rule to count and using SUMPRODUCT function you can easily get the correct count. 

 

Please see attached workbook the example of yellow color is shown with formula there in cell R63

Highlighted

@khaled170 

 

I run a small test and I see that there are a lots of circular references in formulas inside conditional formatting rules. and each conditional formatting rules has multiple logical test and it overwhelm the calculation engine.  Unfortunately, I do not have time to build from scratch a neat table for you, as it takes a lot of time.  If I was you, I would not use nested IF inside the Conditional formatting rules and instead use helper column, this way you avoid too much calculation and circular reference issues. 

 

Highlighted

@khaled170 

 

I assume this is not question related to captioned title of this thread. So, I wouldn't know why it does not work. As i have not seen the file. try to see if you have any broken link among the links. It could be the issue similar to the issue report here

 

https://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/excel-2013-external-links...

 

see the answer from Ashish.

Highlighted

@Jamil Mohammad 

Hi Jamil, thanks for your quick response.

 

I am trying to count the cells in column P in each different section and total these to a specific cell- these have been condition formatted to change colour depending on the values that are put into them, which are summed by the Moulds & Yeasts Columns (Q&R)- for example, I want the total of green cells within P9-18 to be totalled inside C51 and then the orange and red cells from P9-18 in C52 and C53, but I want C51, 52, and 53 to be able to update when changes occur in P9-18 and the colours change. I have managed to add a VBA code where the totals can recognise the colours but I cannot get them to update when the colours change from their value.

I have attached a copy of the workbook without the macro enabled as it was the only way the site would allow me to send it. hope this helps.

Many thanks for your help!

Kind regards,
Chris

Highlighted

@ChrisDempster 

 

Hi Chris,

 

What you are after is possible and this UDF can do that.

 

If you read my earlier messages in this thread, you will notice that I mentioned that for this UDF to work, the conditional formatting rule should be creating using the option "use a formula to determine which cell to format"  

 

to demonstrate, I have changed the rules of conditional formatting for range P9-18 and replaced it with formula. I replaced the rule, between 0 to 9 with =AND(ISNUMBER(P9),LEN(P9)=1) and also the other two colors as you can see it in the attached file.

 

then I placed the UDF as an example in the cell C51 which correctly returns the value.  given this example, you can replicate this for other ranges/cells.

 

plz see attached file.

 

 

 

Highlighted

@Jamil Mohammad 

 

Hi Jamil,

 

Kindly you code does not work in case the conditional format color in Pivot table. Please advice.

 

Highlighted

@wael2005_morganyahoo 

 

Hi, it does work with Conditional formatting on Pivot tables.  please see the example attached.  If it did not work for you. perhaps, you are using the wrong range or your CF is not set as it should.

 

Highlighted

@Jamil Mohammad 

Hi Jamil,

Your posts are very well written and informative, many thanks for sharing your knowledge.

I am however struggling to apply the same code and logic to work on my sheet.

Please refer to the attached document, within the work sheet please go to the 'Labour forecast' tab and see that within column G (Cell G15 to be precise) I am trying to get it to count the coloured cells by condition formatting in the corresponding row.

I am getting 'Name' error return despite having the work book saved as a macro enabled sheet.

any help would be great.

 

much appreicated.

 

Thanks you

Highlighted

@scott_terry87 

 

Thanks. 

 

I looked at the file you shared.   the reason you are getting NAME error, because you have many UDFs in the workbook, but none of them were the one I shared in this thread. which is this one 

 

 

 

Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Application.Volatile
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Font.Color = ColorRng.Font.Color Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function

 

 

 I noticed overlapped conditional formatting rules, as well as inconsistency of ranges used in UDF versus ranges used in the conditional formatting.  So, even if you place the above UDF in the workbook. it will not work, because of the aforementioned issue of inconsistent range and overlapping CF.

 

I thought maybe you are overkilling this by using UDF, whereas the solution you are looking for can be achieved by using built in functions.

 

I rebuild the CF and placed some formulas in the cells O to QG and formatted those cells as custom format ;;; which shows nothing, while there is a value in it. 

 

then applied a CF that if any of those cells hold value of 1 then show green. also those cells has nested IF formula to return 1 if the column E is not blank and is greater than row 14.  then in column G, I have used a COUNTIF formula to count if row of O to QG hold a value of 1.

 

Please see attached and let me know if it works.

 

 

 

 

Highlighted

l@Jamil Mohammad 

Many thanks for the response and taking the time to help me. If only more people were so kind

.

I agree there were certainly some conflicts within the code and CF rules, however I believe the fundamentals of what I am trying to achieve has been lost and subsequently the problem remains unsolved. 

 

please see attached sheet with some additional comments. 

Hopefully it all makes sense, and thank you again. 

For your ease please remember it is within Labour forecast tab, and please note that the it has a 'X&Y' axis frozen pane.


Highlighted

@scott_terry87 

 

Hi again,

 

I have made some changes to the formula in the attached file as per your comments in the workbook.

 

 

Highlighted
Hi, Does this work on 2019 excel?
Highlighted
It works in any version of excel