SOLVED
Home

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%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%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%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%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
Highlighted
Deleted
Not applicable

I installed few add-ins for counting "Conditional formatted" cells but all are generating an instant (one-time) numeric result by activating that command.

 

Also, I tried a code which gives an instant counting of 1 picked color without a total sum of all other colors -in my case 12 colors in a matrix. ("How to use the code to count colored cells and sum their values" - https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/ )

 
My question is: Is there any functional code (or other aproach) which can count and sum all cells by "Conditional formatted" colors, on one sheet, and that the generated result is linked through common formulas? That can be updated/refreshed/code run during the work process as a complete overview of colors of a matrix (and not by picking every color every time as I mentioned).
 
Thanks in advance!

 
114 Replies
Hi, this kind of question is better communicated with a document, stating the problems in short form in the worksheet. Thanks for sharing...
Solution

I came across this post being unanswered. so here is a User Defined Function in VBA

 

to SUM 

 

use this UDF 

Function SumConditionColorCells(CellsRange As Range, ColorRng As Range)
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).Interior.ColorIndex = ColorRng.Interior.ColorIndex 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 + CFCELL.Value
CF3 = CF3 + 1
Next CFCELL
Else
SumConditionColorCells = "NO-COLOR"
Exit Function
End If
SumConditionColorCells = CF2
End Function

 

if you want to Count instead of SUM then use the below UDF

 

Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
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).Interior.ColorIndex = ColorRng.Interior.ColorIndex 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

 

these solutions were provided to the similar question asked by other Excel users and worked for them.

 

For more detail here is the link for that answer 

Thank you very much for you kind and very helpful answer.

I will try the explained method to finally resolve my issue.

Thanks and have a great day !

You are most welcome. Thanks for the feedback.

Hi Jamil,

 

Thank You for posting this solution, I hope that this will also solve my problem With counting the colored cells when using conditinal formatting.

However, so far I am not able to get a number Count, I only get "NO-COLOR". E.g. =CountConditionColorCells(E2;E2:E5) or =CountConditionColorCells(E2:E5;E2) Perhaps I'm completely far out... Could You please let me know how the formula should be written?

Regards,

Julie

 

Hi Julie,

 

please see attached workbook sample that shows how you can use the UDF.

 

please post back, if you have any question.

Hi Jamil,

Not sure why it is not working on my computer, because after pressing "edit" on Your document, it also got error value "#Name?", and You can see on my document Attached how it looks with error #VALUE! I wasn't able to attach the Excel file With Makro enabled, but have of course had the makro enabled file so far.

 

Great if you can advice.

 

Spoiler
 

 

Hi Julie,

 

I checked your file.

 

it was not working because A) you need to enable macros in order to be able to use the UDF.

B)  the sample file you upload, you had only cells for criteria colored, but there wasn't any conditional formatting set in your workbook, that's why It did not work.

 

I have recorded a video that shows how you can set up your conditional formatting.

 

please download the attached video and watch it. also i have attached your workbook with conditional formatting set in it.

 

 

Thank you so much Jamil!

It seemed like something was actually wrong with my original dropdown list, so took some time for me to understand why it didn't work. But now it is finally ok :)
All the best!

I have tried to use the SUM formula here, but keep getting "NO COLOR"...I read through all your posts and tried to troubleshoot, but can't figure out what I'm doing wrong...can you take a look for me?

Hi Michelle,

 

There is a longer thread on the same UDF, there are multiple things that can cause the return of "no color"

 

Please read the limitations of the UDF and other comments here https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditiona...

 

 

haii i already try this but not successful la.. please help me.

Please look at my attachment and my problem at sheet ZONE_WK25(CountColor) and at  column AV10 =countconditioncolorcells($D$10:$AS$10,AV5)

null null

you asked the question in two places, so i replied here on why the UDF did not work for you.
please see my post here. https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditiona...

the code doesnt work with my file..can you help me?

Hi Haziyatul,

I checked your file. The range where the conditional formatting is applied and the range where the UDF uses have to be the same and they are not the same in your excel file.

CF uses $M$9:$EA$56 and UDF uses X9:Y13

so to make this work, you need to either exactly set CF for each of the vertical lines.
however, you would not need to use the UDF for this, you can simply achieve the same result by using the SUMIF or SUMPRODUCT formula. you can see the examples in my earlier posts in the same thread.

Thank you Jamil. but this function doesn't work in case of "Top 10 Item" conditional formatting. Do you have any idea for my problem?

Hi aa aaaa,

Please read my earlier messages, the UDF only works if the conditional formatting is set using formula.

Bad luck!

Is there any solution for my problem?

Open a new question not on this thread but a new question with sample dummy of your data.

How can i open a new question?

Hello.. can you solve the problem for attached file? 

I want to count the specific text colored by conditional formatting column wise. Conditional formatting done by formula.

Please read my earlier messages on the thread and use the UDF to be able to count the conditionally formatted cells.

okay

you have a new question, so the experts would likely to answer your question over there.

Hi Jamil,

 

I had a similar issue to Julie.

I did exactly what you said and the document worked.

 

Since saving, closing the document and today opening it the formula doesn't seem to work when calculating the colored cells.

I get the '#NAME' error.

Any assistance you can offer would be appreciated...?

 

Document attached.

Hi Rowan,

You need to save as macro enabled workbook, when you save as normal workbook format the UDF disappears.

Use either macro enabled workbook format on save as it binary format. Then it will work.

You need to paste the code again into the workbook and save as then the Name error will fix.

I have tried your formulas and they do not work.  I have been stuck on this for 3 weeks now and I'm trying to count my cells as they turn certain colors from Conditional Formatting rules I put in place for my drop down menu's.  If you would be able to assist me that would be wonderful because I am getting tired of watching and reading videos on how it wont work for me formulas.

 

Matthew

Hi Mathew,

I looked at your file. You have set up the conditional formatting using the = cell value option .

The UDF only works if the conditional formatting is set using formulas not through the built-in option of conditional formatting.

You can read about it here
https://techcommunity.microsoft.com/t5/Excel/sum-by-color-when-colors-are-set-by-conditional-formatt...

What you are trying to do is possible, but you have set many conditional formatting rules.

I can help you if you give me the list of the text that are for green color and the list of the texts that are for red color.


these are all of the list and if you can give me which one of them are for green and which one for red. I can build the formula for you.

Acceptable Use Policy
Access Control List
Access Point
Address Resolution Protocol
Address Space Layout Randomization
Advanced Encryption Standard
Advanced Encryption Standard 256 bit
Advanced Persistent Threat
Annualized Loss Expectancy
Annualized Rate of Occurrence
Anti-virus
Application Programming Interface
Application Service Provider
Attribute-based Access Control
Authentication Header
Authentication-Authorization-Accounting
Basic Input/Output System
Bridge Protocol Data Unit
Bring Your Own Device
Business Availability Center
Business Continuity Planning
Business Impact Analysis
Business Partners Agreement
Certificate
Certificate Authority
Challenge Handshake Authentication Protocol
Chief Information Officer
Cipher Block Chaining
Cipher Feedback
Closed-Circuit TeleVision
Common Access Card
Completely Automated Public Turing Test to Tell Computers and Humans Apart
Computer Emergency Response Team
Computer Incident Response Team
Content Management System
Contingency Planning
Continuity of Operations Plan
Controller Area Network
Corporate Owned-Personally Enabled
Corrective Action Report
Counter-Mode/CBC-Mac Protocol
Cyclical Redundancy Check
Triple Digital Encryption Standard

These are all the acronyms I am trying to achieve on my spreadsheet, thank you for your assistance

 

 Matthew

Hi Mathew,

I did not understand your question. The attachment is list of acronyms in pdf.
your Excel file which you posted earlier does not contain of all of these.
you question was related to counting the green and red and to make this work, you need to set up the conditional formatting by yourself. then you can give me the list of the acronyms that are in green category and also the list which are in red category. by then i will be able to give you a formula that handles that count for red and green. Right now, I have not received sufficient information related to your excel problem.

Hi Jamil

 

I stumbled upon your code as I am using conditional formatting formula to highlights my cell. Now I want to count the highlighted cells.

 

Similar to Julie, the VBA returns "no color" instead of the numbers. I have done everything that I can possibly can. 

 

I suspect it doesn't work because I use this formula in my conditional formatting =if(isblank($K$1),0,(search($K$1,A5))). A5 is where my data starts. K1 is where when someone type a text and that text is in the data, it will highlight those texts. Attached is an example (which didn't quite work but you'll get the idea).

 

Thanks for your help. 

hi 7 Heven,

 

You have used Search function and also whole column reference A:A which is not compatible with the UDF.   I have modified the formula and range in the attached example and the UDF works.

 

Hi Jamil

 

I can't believe my luck that you actually replied to my query! Thank you very much!

 

If I modified the formula as per your suggestion and remove the search function, it doesn't work on my master spreadsheet. Is there a way to modify the UDF to make it work with search function? 

 

Many thanks again.

Hi Jamil I was able to successfully use your code to count my conditionally formatted cells but my data changes regularly and the function doesn't automatically refresh. In order for it to pick up the changes i have to refresh the cell with F2 and then enter, any solution for that?

7_Heaven,
you can use search function, but your ranges should not be whole column reference. I have shown in the example in my previous post how you can use it.
mecerrato
first try to check that your workbook calculation setting is "Automatic"
if it is automatic and still did not work then try putting the below line after the first line of UDF.

Application.Volatile

Hi Jamil,

 

I have quite a particular situation regarding the count of CF cells, apart from what i saw exemplified in this topic. (i have tested all the examples in this topic for my application and didn't seemed to work)

 

Basically I have a worksheet that automatically color some cells using Conditional Formatting (some CF colored cells contain text, and some others CF colored cells are blank)

 

I have attached a test sheet with the structure.

 

Well to mention is the fact that in column "H" i will have to count the CF cells for maybe thousands of rows and to be able to see the count for each row in part.

 

Thank you in advance,

Alin

Hi Jamil,

 

I have quite a particular situation regarding the count of CF cells, apart from what i saw exemplified in this topic. (i have tested all the examples in this topic for my application and didn't seemed to work)

 

Basically I have a worksheet that automatically color some cells using Conditional Formatting (some CF colored cells contain text, and some others CF colored cells are blank)

 

I have attached a test sheet with the structure.

 

Well to mention is the fact that in column "H" i will have to count the CF cells for maybe thousands of rows and to be able to see the count for each row in part.

 

Thank you in advance,

Alin

Hi Alin,

 

I checked one of your file and it is clear that you did not use the option of CF "- Use a formula to determine which cells to format"    the UDF only works if you used formula to determine which cells to format.

 

 

I have used one of the worksheets that i found on this topic, on which the count by color worked, and i adapted it for my example.

 

i have used a formula in conditional formatting in order to highlight all the cells that contain "TEXT".

 

But somehow it is showing only a count of three for all the three rows that i used for example.

 

I missed something for sure.

 

Attached the worksheet.

hi Alin,

 

I looked at your file.  The issue was that you had CF applied range A10 to A10:E12  while your UDF were using for each row.  the UDF range and the CF range should match, that is when it will work.

 

I have attached the workbook and also i have attached a recorded video on how to use the paintbrush to apply CF to the other cells without the hassle of recreating CF for each row.

thank you so much for this Jamil

I got it finally :)

 

I wish you all the best

 

 

Hi Jamil,

 

Please check my attached file which i want to count the colored CF cells by row. Problem is there are different formulas in each column, if i want to count the result by rows, i get an error. If i count by column wise it got results. Please help so i can count it by rows.

Thanks in advance.

Hi Roland,

If you read through my earlier posts. I have clearly mentioned that UDF only works if the conditional formating is set using Formula. you have used formulas and non-formula options to set your conditional formatting. that is the first issue that is causing the UDF not to work.

the second issue is that your are using the whole column references in your CF which is not going to work.

third issue is that your CF is set by column and you are trying to do the count in UDF using row. If you look at the example I illustrated in answer to Alin. you can see that i modified the CR using painbrush to reflect the rows.

so you need to fix the three of the issues i pointed, so that the UDF would work.

Hi Jamil,

The example that you helped me with worked like a charm but only for one conditional format formula applied for a range of cells.

Now i have a case in which i want to count all the CF cells in a row, but with multiple CF formulas.

I followed all the steps that you suggested (i hope i didn't missed something out).

I have attached the worksheet.

 

Thank you in advance

Alin

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies