{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Array formulas - examples","Count with criteria","Several funсtions","Count characters","Sum n-th rows","Different calculations","User-defined functions"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":0,"defined_names":1,"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":"Array formulas - examples","index":0,"n_rows":20,"n_cols":7,"rows_kept":20,"cut":false,"hidden":false,"merged":[[3,1,3,2],[5,1,5,2]]}
[]
[]
[]
[null,"Sample Workbook to Array Formulas"]
[]
[null,"The workbook shows how to use array formulas in Excel to perform multiple calculations at a time."]
[null,"Author","Ablebits.com"]
[null,"Last update","2020-11-19"]
[null,"Tutorial URL","Excel array formula examples"]
[]
[null,"Examples:"]
["• ","Count cells that meet certain conditions"]
["• ","Use several functions in array formula"]
["• ","Count characters in a range"]
["• ","Sum values in every Nth row"]
["• ","Perform different calculations within different ranges"]
["• ","User-defined functions in Excel array formulas"]
[]
[]
[null,null,null,null,null,null," "]

{"sheet":"Count with criteria","index":1,"n_rows":10,"n_cols":5,"rows_kept":10,"cut":false,"hidden":false,"merged":[[0,4,1,4]]}
["Planned","Gained",null,"Gained>Planned \n",{"$f":"SUM((B2:B10>=A2:A10)*(B2:B10>0))","v":5}]
[33,32,null,"Gained>=0"]
[9,16]
[8,6]
[21,30]
[27,25]
[16,16]
[13,43]
[0,0]
[19,49]

{"sheet":"Several funсtions","index":2,"n_rows":9,"n_cols":6,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Seller","Product","Sales",null,"Seller","Mike"]
["Mike","Apples",10,null,"Product","Apples"]
["Sally","Lemons",50]
["Neal","Lemons",20,null,"Max",{"$f":"MAX(IF(($A$2:$A$9=$F$1)*($B$2:$B$9=$F$2), $C$2:$C$9,\"\"))","v":30}]
["Mike","Apples",30,null,"Min",{"$f":"MIN(IF(('Several funсtions'!$A$2:$A$9=$F$1)*('Several funсtions'!$B$2:$B$9=$F$2),'Several funсtions'!$C$2:$C$9, \"\"))","v":10}]
["Neal","Lemons",40,null,"Average",{"$f":"AVERAGE(IF(($A$2:$A$9=$F$1)*($B$2:$B$9=$F$2),$C$2:$C$9, \"\"))","v":20}]
["Sally","Apples",10,null,"Total",{"$f":"SUM(IF(($A$2:$A$9=$F$1)*($B$2:$B$9=$F$2),$C$2:$C$9, \"\"))","v":60}]
["Mike","Lemons",10]
["Mike","Apples",20]

{"sheet":"Count characters","index":3,"n_rows":9,"n_cols":6,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Date","Completed orders",null,"Identifier:","K"]
["2015-02-17","A101, B107, K198",null,"Count:",{"$f":"SUM((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,E1,\"\")))/LEN(E1))","v":4}]
["2015-02-18","A165, K198",null,"Total chars in B2:B5:",{"$f":"SUM(LEN(B2:B5))","v":52}]
["2015-02-19","K189, A145, K125"]
["2015-02-20","B167, A199"]
[]
[]
[]
[null,null,null,null,null," "]

{"sheet":"Sum n-th rows","index":4,"n_rows":7,"n_cols":5,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
["Product","Sales",null,"Sum every Nth row:",3]
["Apples",10,null,"Total:",{"$f":"SUM((--(MOD((ROW($B$2:$B$7)-ROW($B$2)), E1)=E1-1))*($B$2:$B$7))","v":30}]
["Peaches",30]
["Apples",20]
["Peaches",10]
["Apples",40]
["Peaches",10]

{"sheet":"Different calculations","index":5,"n_rows":9,"n_cols":5,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Unit Quantity","Price per unit",null,"Unit Quantity","Price per unit"]
["1 to 10",20,null,"1 to 10",20]
["11 to 19",18,null,"11 to 19",18]
["20 to 49",16,null,"20 to 49",16]
["50 to 100",13,null,"50 to 100",13]
["101 to 200",12,null,"101 to 200",12]
[]
["Qty.",100,null,"Qty.",201]
["Total",{"$f":"SUM($B$8*($B$2:$B$6)*(--($B$8>=VALUE(LEFT($A$2:$A$6,FIND(\" \",$A$2:$A$6)))))*(--($B$8<=VALUE(RIGHT($A$2:$A$6,LEN($A$2:$A$6)-FIND(\" to \",$A$2:$A$6)-LEN(\" to\" ))))))","v":1300},null,"Total",{"$f":"IF(AND(E8>=VALUE(LEFT(D2,FIND(\" \",D2))), E8<=VALUE(RIGHT(D6,LEN(D6)-FIND(\" to \",D6)-LEN(\" to\" )))), SUM(E8*(E2:E6)*(--(E8>=VALUE(LEFT(D2:D6,FIND(\" \",D2:D6)))))*(--(E8<=VALUE(RIGHT(D2:D6,LEN(D2:D6)-FIND(\" to \",D2:D6)-LEN(\" to\" )))))), \"Out of range\")","v":"Out of range"}]

{"sheet":"User-defined functions","index":6,"n_rows":10,"n_cols":6,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Seller","Product","Sales",null,"Seller","Neal"]
["Mike","Apples",10,null,"Green sales",{"$f":"SUM(--($A$2:$A$10=$F$1)*($C$2:$C$10)*(--(GetCellColor($C$2:$C$10)=GetCellColor($E$2))))","v":105}]
["Sally","Lemons",30,null,"Yellow sales",{"$f":"SUM(--($A$2:$A$10=$F$1)*($C$2:$C$10)*(--(GetCellColor($C$2:$C$10)=GetCellColor($E$3))))","v":15}]
["Neal","Kiwi",20]
["Neal","Apples",15]
["Neal","Kiwi",40]
["Sally","Apples",10]
["Mike","Kiwi",30]
["Mike","Lemons",12]
["Neal","Apples",45]