{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Colored cells - Examples","Count and sum by fill color","Count and sum by font color","Accross worbook","Conditionally formatted","Get color code"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":1,"defined_names":2,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Colored cells - Examples","index":0,"n_rows":29,"n_cols":3,"rows_kept":29,"cut":false,"hidden":false,"merged":[[3,1,3,5],[5,1,5,2]]}
[]
[]
[]
[null,"Count and Sum Cells by Color "]
[]
[null,"The workbook shows how to sum and count colored cells in Excel by using custom functions and macros."]
[null,"Author","Ablebits.com"]
[null,"Last update","2022-08-19"]
[null,"Tutorial URL","How to count and sum colored cells in Excel"]
[]
[null,"The workbook contains the following custom functions:"]
[null,"CountCellsByColor -counts cells by fill color."]
[null,"CountCellsByFontColor -counts cells by font color."]
[null,"SumCellsByColor -sums values by fill color."]
[null,"SumCellsByFontColor -sums values by font color."]
[null,"WbkCountByColor - counts colored cells in the entire workbook."]
[null,"WbkSumByColor - sums colored cells in the entire workbook."]
[null,"GetCellColor - returns fill color code."]
[null,"GetFontColor - returns font color code."]
[]
[null,"The workbook contains the following macro:"]
[null,"SumCountByConditionalFormat - counts and sums conditionally formatted cells."]
[]
[null,"Examples:"]
["• ","Count and sum by fill color"]
["• ","Count and sum by font color"]
["• ","Count and sum colored cells across entire workbook"]
["• ","Count and sum conditionally formatted cells "]
["• ","Get color code "]

{"sheet":"Count and sum by fill color","index":1,"n_rows":22,"n_cols":12,"rows_kept":22,"cut":false,"hidden":false,"merged":[[2,10,2,11],[7,10,7,11]]}
["Item","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"]
["Apples",158,156,234,214,171,258,169,278]
["Apricots",320,342,112,273,107,325,386,335,null,"Count by color"]
["Avocado",221,124,215,111,311,144,328,380,null,null,{"$f":"CountCellsByColor(B2:I22, K4)","v":29}]
["Bananas",207,363,225,265,130,263,109,309,null,null,{"$f":"CountCellsByColor(B2:I22, K5)","v":31}]
["Cherries",343,222,129,307,353,302,236,296]
["Coconut",331,244,366,110,323,338,247,155]
["Dates",400,252,188,132,116,187,368,284,null,"Sum by color"]
["Fig",335,254,356,270,293,179,134,330,null,null,{"$f":"SumCellsByColor(B2:I22, K9)","v":3573}]
["Grapefruit",210,216,279,297,253,321,392,277,null,null,{"$f":"SumCellsByColor(B2:I22, K10)","v":11616}]
["Grapes",360,174,283,278,379,118,114,232]
["Kiwi",161,294,393,240,159,139,271,211]
["Lemons",260,276,375,167,106,152,115,103]
["Lime",268,385,284,185,272,233,382,135]
["Mango",348,206,358,104,326,350,226,167]
["Melon",292,330,378,181,364,150,223,251]
["Nectarine",177,149,367,123,312,162,339,176]
["Oranges",327,286,303,170,133,246,376,227]
["Papaya",277,142,371,362,180,288,183,369]
["Peach",369,377,351,389,176,126,396,220]
["Pear",341,390,381,217,196,295,245,114]
["Plum",313,184,145,131,380,322,168,118]

{"sheet":"Count and sum by font color","index":2,"n_rows":22,"n_cols":12,"rows_kept":22,"cut":false,"hidden":false,"merged":[[2,10,2,11],[7,10,7,11]]}
["Item","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"]
["Apples",158,156,234,214,171,258,169,278]
["Apricots",320,342,112,273,107,325,386,335,null,"Count by font color"]
["Avocado",221,124,215,111,311,144,328,380,null,"sample 1",{"$f":"CountCellsByFontColor(B2:I22,K4)","v":29}]
["Bananas",207,363,225,265,130,263,109,309,null,"sample 2",{"$f":"CountCellsByFontColor(B2:I22, K5)","v":31}]
["Cherries",343,222,129,307,353,302,236,296]
["Coconut",331,244,366,110,323,338,247,155]
["Dates",400,252,188,132,116,187,368,284,null,"Sum by font color"]
["Fig",335,254,356,270,293,179,134,330,null,"sample 1",{"$f":"sumCellsByFontColor(B2:I22, K9)","v":3573}]
["Grapefruit",210,216,279,297,253,321,392,277,null,"sample 2",{"$f":"sumCellsByFontColor(B2:I22, K10)","v":11616}]
["Grapes",360,174,283,278,379,118,114,232]
["Kiwi",161,294,393,240,159,139,271,211]
["Lemons",260,276,375,167,106,152,115,103]
["Lime",268,385,284,185,272,233,382,135]
["Mango",348,206,358,104,326,350,226,167]
["Melon",292,330,378,181,364,150,223,251]
["Nectarine",177,149,367,123,312,162,339,176]
["Oranges",327,286,303,170,133,246,376,227]
["Papaya",277,142,371,362,180,288,183,369]
["Peach",369,377,351,389,176,126,396,220]
["Pear",341,390,381,217,196,295,245,114]
["Plum",313,184,145,131,380,322,168,118]

{"sheet":"Accross worbook","index":3,"n_rows":10,"n_cols":3,"rows_kept":10,"cut":false,"hidden":false,"merged":[[2,1,2,2],[7,1,7,2]]}
[]
[]
[null,"Count cells by color"]
[null,null,{"$f":"WbkCountByColor(B4)","v":34}]
[null,null,{"$f":"WbkCountByColor(B5)","v":36}]
[]
[]
[null,"Sum by color"]
[null,null,{"$f":"WbkSumByColor(B9)","v":3573}]
[null,null,{"$f":"WbkSumByColor(B10)","v":11616}]

{"sheet":"Conditionally formatted","index":4,"n_rows":22,"n_cols":9,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
["Item","Jan","Feb","Mar","Apr"]
["Apples",158,156,234,214,null,"Sample color",null,"How to sum and count conditionally formatted cells using the macro:"]
["Apricots",320,342,112,273,null,null,null,"1. Select range B2:E22."]
["Avocado",221,124,215,111,null,null,null,"2. Press Alt + F8, select the SumCountByConditionalFormat macro in the list, and click Run."]
["Bananas",207,363,225,265,null,null,null,"3. In a small dialog box that will pop up, select cell G3 with the sample color, and click OK."]
["Cherries",343,222,129,307]
["Coconut",331,244,366,110]
["Dates",400,252,188,132]
["Fig",335,254,356,270]
["Grapefruit",210,216,279,297]
["Grapes",360,174,283,278]
["Kiwi",161,294,393,240]
["Lemons",260,276,375,167]
["Lime",268,385,284,185]
["Mango",348,206,358,104]
["Melon",292,330,378,181]
["Nectarine",177,149,367,123]
["Oranges",327,286,303,170]
["Papaya",277,142,371,362]
["Peach",369,377,351,389]
["Pear",341,390,381,217]
["Plum",313,184,145,131]

{"sheet":"Get color code","index":5,"n_rows":18,"n_cols":9,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["Fill Color","Decimal","Hexadecimal ",null,"Font Color","Code","Hexadecimal "]
[null,{"$f":"GetCellColor(A2)","v":49407},{"$f":"\"#\"&DEC2HEX(GetCellColor(A2))","v":"#C0FF"},null,"Color 1",{"$f":"GetFontColor(E2)","v":255},{"$f":"\"#\"&DEC2HEX(GetFontColor(E2))","v":"#FF"}]
[null,{"$f":"GetCellColor(A3)","v":4697456},{"$f":"\"#\"&DEC2HEX(GetCellColor(A3))","v":"#47AD70"},null,"Color 2",{"$f":"GetFontColor(E3)","v":192},{"$f":"\"#\"&DEC2HEX(GetFontColor(E3))","v":"#C0"}]
[null,{"$f":"GetCellColor(A4)","v":1137094},{"$f":"\"#\"&DEC2HEX(GetCellColor(A4))","v":"#1159C6"},null,"Color 3",{"$f":"GetFontColor(E4)","v":49407},{"$f":"\"#\"&DEC2HEX(GetFontColor(E4))","v":"#C0FF"}]
[null,{"$f":"GetCellColor(A5)","v":16777215},{"$f":"\"#\"&DEC2HEX(GetCellColor(A5))","v":"#FFFFFF"},null,"Color 4",{"$f":"GetFontColor(E5)","v":5287936},{"$f":"\"#\"&DEC2HEX(GetFontColor(E5))","v":"#50B000"}]
[null,{"$f":"GetCellColor(A6)","v":15917529},{"$f":"\"#\"&DEC2HEX(GetCellColor(A6))","v":"#F2E1D9"},null,"Color 5",{"$f":"GetFontColor(E6)","v":12874308},{"$f":"\"#\"&DEC2HEX(GetFontColor(E6))","v":"#C47244"}]
[null,{"$f":"GetCellColor(A7)","v":14348258},{"$f":"\"#\"&DEC2HEX(GetCellColor(A7))","v":"#DAEFE2"},null,"Color 6",{"$f":"GetFontColor(E7)","v":24704},{"$f":"\"#\"&DEC2HEX(GetFontColor(E7))","v":"#6080"}]
[null,{"$f":"GetCellColor(A8)","v":14083324},{"$f":"\"#\"&DEC2HEX(GetCellColor(A8))","v":"#D6E4FC"},null,"Color 7",{"$f":"GetFontColor(E8)","v":0},{"$f":"\"#\"&DEC2HEX(GetFontColor(E8))","v":"#0"}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null," "]