{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Dataset","OFFSET Function","INDEX_MATCH Function","LARGE_COLUMN Function","COUNTIF Function","Sum Last 5 Values"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"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":"Dataset","index":0,"n_rows":10,"n_cols":9,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,2,1,8],[3,2,3,8]]}
[]
[null,null,"How to Sum of Last 5 Values in Excel"]
[]
[null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021]
[null,"AC",300,350,400,425,450,450,500]
[null,"TV",200,250,200,500,300,200,200]
[null,"Monitor",150,180,200,220,220,250,250]
[null,"Fan",70,80,100,120,120,100,110]
[null,"Speaker",50,50,55,60,70,70,80]

{"sheet":"OFFSET Function","index":1,"n_rows":10,"n_cols":10,"rows_kept":10,"cut":false,"hidden":false,"merged":[[9,9,9,10],[1,2,1,8],[4,9,4,10],[3,2,3,8],[5,9,5,10],[6,9,6,10],[7,9,7,10],[8,9,8,10]]}
[]
[null,null,"Sum Last 5 Values Using OFFSET Function"]
[]
[null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values"]
[null,"AC",300,350,400,425,450,450,500,{"$f":"SUM(OFFSET($C6,0,COUNT($C6:$I6)-1,,-5))","v":2225}]
[null,"TV",200,250,200,500,300,200,200,{"$f":"SUM(OFFSET($C7,0,COUNT($C7:$I7)-1,,-5))","v":1400}]
[null,"Monitor",150,180,200,220,220,250,250,1140]
[null,"Fan",70,80,100,120,120,100,110,550]
[null,"Speaker",50,50,55,60,70,70,80,335]

{"sheet":"INDEX_MATCH Function","index":2,"n_rows":10,"n_cols":10,"rows_kept":10,"cut":false,"hidden":false,"merged":[[8,9,8,10],[9,9,9,10],[1,2,1,8],[3,2,3,8],[4,9,4,10],[5,9,5,10],[6,9,6,10],[7,9,7,10]]}
[]
[null,null,"Using INDEX-MATCH Function"]
[]
[null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values"]
[null,"AC",300,350,400,425,450,450,500,{"$f":"SUM(INDEX($C6:$I6,MATCH(9.9E+307,$C6:INDEX($C6:$I6,MATCH(9.9E+307,$C6:$I6)-4))):$I6)","v":2225}]
[null,"TV",200,250,200,500,300,200,200,{"$f":"SUM(INDEX($C7:$I7,MATCH(9.9E+307,$C7:INDEX($C7:$I7,MATCH(9.9E+307,$C7:$I7)-4))):$I7)","v":1400}]
[null,"Monitor",150,180,200,220,220,250,250,{"$f":"SUM(INDEX($C8:$I8,MATCH(9.9E+307,$C8:INDEX($C8:$I8,MATCH(9.9E+307,$C8:$I8)-4))):$I8)","v":1140}]
[null,"Fan",70,80,100,120,120,100,110,{"$f":"SUM(INDEX($C9:$I9,MATCH(9.9E+307,$C9:INDEX($C9:$I9,MATCH(9.9E+307,$C9:$I9)-4))):$I9)","v":550}]
[null,"Speaker",50,50,55,60,70,70,80,{"$f":"SUM(INDEX($C10:$I10,MATCH(9.9E+307,$C10:INDEX($C10:$I10,MATCH(9.9E+307,$C10:$I10)-4))):$I10)","v":335}]

{"sheet":"LARGE_COLUMN Function","index":3,"n_rows":10,"n_cols":10,"rows_kept":10,"cut":false,"hidden":false,"merged":[[8,9,8,10],[9,9,9,10],[1,2,1,8],[3,2,3,8],[4,9,4,10],[5,9,5,10],[6,9,6,10],[7,9,7,10]]}
[]
[null,null,"Using LARGE & COLUMN Functions"]
[]
[null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values"]
[null,"AC",300,350,400,425,450,450,500,{"$f":"SUM((COLUMN($C6:$I6)>=LARGE(COLUMN($C6:$I6)*(N(+$C6:$I6)>0),5))*N(+$C6:$I6))","v":2225}]
[null,"TV",200,250,200,500,300,200,200,{"$f":"SUM((COLUMN($C7:$I7)>=LARGE(COLUMN($C7:$I7)*(N(+$C7:$I7)>0),5))*N(+$C7:$I7))","v":1400}]
[null,"Monitor",150,180,200,220,220,250,250,{"$f":"SUM((COLUMN($C8:$I8)>=LARGE(COLUMN($C8:$I8)*(N(+$C8:$I8)>0),5))*N(+$C8:$I8))","v":1140}]
[null,"Fan",70,80,100,120,120,100,110,{"$f":"SUM((COLUMN($C9:$I9)>=LARGE(COLUMN($C9:$I9)*(N(+$C9:$I9)>0),5))*N(+$C9:$I9))","v":550}]
[null,"Speaker",50,50,55,60,70,70,80,{"$f":"SUM((COLUMN($C10:$I10)>=LARGE(COLUMN($C10:$I10)*(N(+$C10:$I10)>0),5))*N(+$C10:$I10))","v":335}]

{"sheet":"COUNTIF Function","index":4,"n_rows":13,"n_cols":10,"rows_kept":13,"cut":false,"hidden":false,"merged":[[8,9,8,10],[9,9,9,10],[12,1,12,10],[1,2,1,8],[3,2,3,8],[4,9,4,10],[5,9,5,10],[6,9,6,10],[7,9,7,10]]}
[]
[null,null,"Using COUNTIF Function"]
[]
[null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values"]
[null,"AC",300,350,400,425,450,450,500,{"$f":"IF(COUNTIF($C6:$I6,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C6,0,LARGE(IF($C6:$I6>0,COLUMN($C6:$I6)-COLUMN($C6)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")","v":2225}]
[null,"TV",200,250,200,500,300,200,200,{"$f":"IF(COUNTIF($C7:$I7,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C7,0,LARGE(IF($C7:$I7>0,COLUMN($C7:$I7)-COLUMN($C7)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")","v":1400}]
[null,"Monitor",150,180,200,220,220,250,250,{"$f":"IF(COUNTIF($C8:$I8,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C8,0,LARGE(IF($C8:$I8>0,COLUMN($C8:$I8)-COLUMN($C8)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")","v":1140}]
[null,"Fan",70,80,100,120,120,100,110,{"$f":"IF(COUNTIF($C9:$I9,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C9,0,LARGE(IF($C9:$I9>0,COLUMN($C9:$I9)-COLUMN($C9)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")","v":550}]
[null,"Speaker",50,50,55,60,70,70,80,{"$f":"IF(COUNTIF($C10:$I10,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C10,0,LARGE(IF($C10:$I10>0,COLUMN($C10:$I10)-COLUMN($C10)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")","v":335}]
[]
[null,null,null,null,null,"Formula"]
[null,{"$f":"_xlfn.FORMULATEXT(J6)","v":"=IF(COUNTIF($C6:$I6,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C6,0,LARGE(IF($C6:$I6>0,COLUMN($C6:$I6)-COLUMN($C6)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")"}]

{"sheet":"Sum Last 5 Values","index":5,"n_rows":10,"n_cols":11,"rows_kept":10,"cut":false,"hidden":false,"merged":[[3,2,3,8],[1,2,1,8]]}
[]
[null,null,"Sum Last 5 Values Using VBA Code"]
[]
[null,null,"Sales Per Item in Year"]
[null,"Item-TV",2015,2016,2017,2018,2019,2020,2021,"No. of Last Value","Output"]
[null,"AC",300,350,400,425,450,450,500,5,2225]
[null,"TV",200,250,200,500,300,200,200,5,1400]
[null,"Monitor",150,180,200,220,220,250,250,5,1140]
[null,"Fan",70,80,100,120,120,100,110,5,550]
[null,"Speaker",50,50,55,60,70,70,80,5,335]