Amit Tandon

#Excel SUM Smallest 3 of 7 Non Contiguous Cells (A2,A4,A5, A7,A8, A11,A12), skip blank cells. Array/CSE formula: =SUM(SMALL(IF(COUNTIF(INDIRECT({"A2","A4","A5","A7","A8","A11","A12"}),"<>"),SUMIF(INDIRECT({"A2","A4","A5","A7","A8","A11","A12"}),"<>")),ROW(1:3)))

#Excel SUM Smallest 3 Values in Non-Contiguous Cells. SUM Smallest 3 of 7 Non Contiguous Cells (A2,A4,A5, A7,A8, A11,A12), skip blank cells: =SUM(SMALL(IF(COUNTIF(OFFSET(A2,{0,2,3,5,6,9,10},),"<>"),SUBTOTAL(9,OFFSET(A2,{0,2,3,5,6,9,10},))),{1,2,3}))

#Excel SUM Smallest 3 Non-Contiguous Cells. SUM Smallest 3 of 7 Non Contiguous Cells (A2,A4,A5, A7,A8, A11,A12) w/ values >0.Array =SUM(SMALL(IF(SUMIF(INDIRECT({"A2","A4","A5","A7","A8","A11","A12"}),">0")>0,SUMIF(INDIRECT({"A2","A4","A5","A7","A8","A11","A12"}),">0")),ROW(1:3)))

#Excel SUM Smallest 3 Values in Non-Contiguous Cells. SUM Smallest 3 of 7 Non Contiguous Cells (A2,A4,A5, A7,A8, A11,A12) having values greater than zero: =SUM(SMALL(IF(SUBTOTAL(9,OFFSET(A2,{0,2,3,5,6,9,10},))>0,SUBTOTAL(9,OFFSET(A2,{0,2,3,5,6,9,10},))),{1,2,3}))

#Excel SUM Smallest 3 Values in Non-Contiguous Cells.SUM Smallest 3 of 7 Non Contiguous Cells (A2,A4,A5, A7,A8, A11,A12) having values greater than zero.Array =SUM(SMALL(IF(CHOOSE({1;2;3;4;5;6;7},A2,A4,A5,A7,A8,A11,A12)>0,CHOOSE({1;2;3;4;5;6;7},A2,A4,A5,A7,A8,A11,A12)),ROW(1:3)))

#Excel SUM Largest 4 Values in Non-Contiguous Cells. SUM largest 4 values in the 2nd row (B2:X2) where 1st row (B1:X1) = 1 or 2. =SUM(AGGREGATE(14,6,(B2:X2)/((B1:X1={1;2})*(B2:X2<>"")),{1,2,3,4}))

#Excel SUM Largest 4 Values in Non-Contiguous Cells (even intervals). SUM largest 4 values in 2nd row cells: B2,C2, F2,G2, J2,K2, N2,O2, R2,S2, V2,W2 (consider 1st 2 cells, skip next 2). Array: =SUM(LARGE(IF(B2:X2<>"",IF(MOD(COLUMN(B2:X2)-COLUMN(B2)+1,4)={1;2},B2:X2)),{1,2,3,4}))

#Excel SUM Largest 4 Values in Non-Contiguous Cells of a Row. Non-Contiguous cells with even intervals - SUM largest 4 values in the 2nd row cells of B2, F2, J2, N2, R2 & V2 - Array/CSE: =SUM(LARGE(IF(B2:X2<>"",IF(MOD(COLUMN(B2:X2)-COLUMN(B2)+1,4)=1,B2:X2)),TRANSPOSE(ROW(1:4))))

#Excel SUM Largest 4 Values in Non-Contiguous Cells of a Row. Non-Contiguous cells with even intervals - SUM largest 4 values in the 2nd row cells of B2, F2, J2, N2, R2 & V2 - Array/CSE Formula: =SUM(LARGE(IF(B2:X2<>"",IF(MOD(COLUMN(B2:X2)-COLUMN(B2)+1,4)=1,B2:X2)),{1,2,3,4}))

#Excel Assign separate Values to Non-Contiguous Cells & return SUM. In range A2:A10 if "Yes" return SUM: if 2nd or 8th cells are "Yes" add 10 for each; if any other cells are "Yes" add 5 for each: =SUMPRODUCT((A2:A10="Yes")*(ISNUMBER(MATCH(ROW(A2:A10)-ROW(A2)+1,{2,8},0))*5+5))

#Excel SUM non-contiguous Ranges: 4 (B2) consecutive col A cells, skip 2 (B4) cells, start frm cell A4 ie 3rd Position (B3). For rng A2:A100, cells considered:A4:A7,A10:A13,A16:A19 ...=SUMPRODUCT(((ROW(A2:A100)-ROW(A2)+1)>=B3)*(MOD(ROW(A2:A100)-ROW(A2)-B3+1,B2+B4)<=B2-1)*A2:A100)

#Excel SUM Non-Contiguous Ranges of a Column - in consecutive col D cells, SUM every 4 (B3=4) consecutive col A cells, skipping every 2 (B4=2) cells, & starting from cell A4 ie. 3rd Position (B2=3). Enter in cell B2, copy down: =SUM(OFFSET(A$2,(ROW(A1)-1)*(B$3+B$4)+B$2-1,0,B$3))

#Excel Using SUMIF with Dates Range. Using SUMIF for Dates in Calendar Year - SUM Col D where Col A dates are in the Year (01 Jan-31 Dec) of cell I37 date & Col B cells are not blank: =SUMIFS(D2:D20,A2:A20,">="&DATE(YEAR(I37),1,1),A2:A20,"<="&DATE(YEAR(I37),12,31),B2:B20,"<>")

#Excel Using SUMIF with Dates Range. Using SUMIF for Dates within a Month / Year - SUM Col D where Col A dates are in the Month & Year of cell I37 & and Col B cells are not blank: =SUMIFS(D2:D20,A2:A20,">"&EOMONTH(I37,-1),A2:A20,"<="&EOMONTH(I37,0),B2:B20,"<>")

#Excel SUMIFS using tilde wild card ("~") allows searching words that contain a wild card (* or ?). Tilde before asterisk does not treat asterisk as a wildcard. SUM col D for col C values which start with "*F" and where col B = "Metro" =SUMIFS(D2:D20,C2:C20,"~*F*",B2:B20,"Metro")

#Excel SUMIFS using wildcard Question mark (?) which matches any single character: SUM column D for column C values with any single character bewteen "I" & "eland" & non-blanks in column B: =SUMIFS(D2:D20,C2:C20,"I?eland",B2:B20,"<>")

#Excel SUMIFS using wildcard Asterisk (*) which matches any sequence of characters: SUM column D for column C values starting with "U" where column B cells are non-blank: =SUMIFS(D2:D20,C2:C20,"U*",B2:B20,"<>")

#Excel SUMIF Non-Contiguous Ranges. Search ranges "B2:B5","B9","B14:B16" for "Car" criteria, SUM ranges "D2:D5","D9","D14:D16".Named rng "RngNonContg" (F12:F14)- "B2:B5","B9","B14:B16" in F12,F13,F14 =SUMPRODUCT(SUMIF(INDIRECT(RngNonContg),"Car",OFFSET(INDIRECT(RngNonContg),,2)))

#Excel SUMIF Non-Contiguous Cell References for "Range" / "Criteria". Searches 3 non-contiguous ranges "B2:B5","B9","B14:B16" for "Car" criteria, & corresponding SUM range ("D2:D5","D9","D14:D16"): =SUM(SUMIF(OFFSET(B2,{0;7;12},,{4;1;3}),"Car",OFFSET(D2,{0;7;12},,{4;1;3})))

#Excel SUMIF Non-Contiguous Cell References for "Range". Searches 3 non-contiguous ranges "B2:B5","B9","B14:B16" for "Car" criteria, & corresponding SUM range ("D2:D5","D9","D14:D16"): =SUMPRODUCT(SUMIF(INDIRECT({"B2:B5","B9","B14:B16"}),"Car",INDIRECT({"D2:D5","D9","D14:D16"})))