## SUM Smallest 3 Values in Non-Contiguous Cells with Uneven Intervals

SUM Smallest 3 Values in Non-Contiguous Cells with Uneven Intervals     SUM of Smallest 3 Non Contiguous Cells (with values above zero or non-blank cells) with Uneven Intervals SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12) – Multiple Methodologies are illustrated    Cell D17 and D27 formulas: Both formulas – Sum...

## Count Occurrences of a Value in Column, as a whole or in part

Count Occurrences of a Value in all cells of a Column, either individually or part of a string Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup with VLookup Excel function. 3. Left Lookup, with Index, Match & Offset Excel functions. 4. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value. Count...

## Sum Diagonal Cells in a Range

SUM Diagonal Cells in a Range     SUM / Count Diagonal Cells in a Range Diagonal cells are considered starting from the top-left cell of the range     Cell G4 formula: SUM diagonal cells in range “B4:E9” starting from cell “B4” ie. top-left cell of the range – cells B4, C5, D6, E7.      ———————————————————————————————– ...

## AVERAGE of Non Contiguous Cells which are Unevenly Distributed

AVERAGE of Non Contiguous Cells which are Unevenly Distributed     AVERAGE of Non Contiguous Cells which are Unevenly Distributed    Cell D9 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10) having values greater than zero.      ———————————————————————————————–   Average Non-Contiguous Cells, Unevenly Distributed    Cell D9 formula: Average 6...

## Count Cells in Non-Contiguous Ranges, where a value occurs in whole or in part

Count cells where a value appears atleast once (in whole or as part of a string), in Multiple Non-Contiguous Ranges Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup with VLookup Excel function. 3. Left Lookup, with Index, Match & Offset Excel functions. 4. Vlookup Multiple Values – Return MULTIPLE corresponding values for...

## Countif Multiple Criteria, relate Criteria to a Value, return SUM

Count Occurrences of Multiple Criteria in a Column, relate each Criteria to a Number and return SUM     Countif with Multiple Criteria – multiply each occurrence with a value corresponding to the respective Criteria and return SUM    Cell D7 formula: Count occurrences of “Apple” (C2), “Banana” (C3) & “Pear” (C4) in col A, return...

## Average of first 10 values in a column, Ignore Error Values

Average of first 10 values in a column, Ignore Error Values, Exclude / Include Zeros & Blank Cells     Average of first 10 values in a column, Ignore Error Values    Cell C8 formula:  Average of the first 10 values in column A, ignore errors, includes zeros, excludes blank cells.     ———————————————————————————————–      Average 1st 10...

## Sumif to Exclude Cells Hidden by a Filter

SUMIF using SUBTOTAL function to Exclude Cells Hidden by a Filter      Sumif / Countif using SUBTOTAL function to Exclude / Include Cells Hidden by a Filter.    Cell D6 formula: SUMIF to EXCLUDE cells hidden by using Filter.       ———————————————————————————————–   SUMIF Exclude Hidden Filtered Rows     Cell D6 formula SUMIF to EXCLUDE cells...

## Average of Last 20 Positive values only in a Column

Average of Last 20 column values, only Positives / Negatives     Average of last 20 positive values (zero and above) in column A    Cell G4 formula:  Average of last 20 positive values (zero and above) in column A.    ———————————————————————————————–            Cell G4 formula: Average of last 20 positive values (zero and above) in...

## Average Largest 5 column values, per Matching Id

Average of Largest 5 Values in a Column, per Matching Id     Average of Largest 5 Values in column B, corresponding to the respective Id in column A.    Cell F2 formula:  Average of Largest 5 Values in column B, corresponding to the respective Id in column A – Blank cells are ignored, and zeros are considered for Average. ...

## SUMIF with Date Range, Multiple Month Names / Numbers & Year

SUMIF with Date Range, Multiple Month Names / Numbers & Year      Return SUM of values corresponding to One or Multiple Months, Date Range, and Multiple Names – using SUM, SUMIFS or SUMRODUCT.    Cell F6 formula: Return SUM of column C values, where column A month numbers are either 4, 1, or...

## Average of Consecutive Negative Numbers

Average of Consecutive Negative Numbers in a Column     Average of Consecutive Negative Numbers in column A.    Cell C3 formula:  Average of Consecutive Negative Numbers (ie. if Consecutive more than Once atleast) in Column A.    ———————————————————————————————–    Average Consecutive Negative Numbers     Cell C3 formula: Average of Consecutive Negative Numbers (ie. if Consecutive more than Once atleast) in...

## SUM Max values of Each Row, in a Multiple Column Range

SUM Max values of Each Row, in a Multiple Column Range     Return SUM of Max values in Each Row, for a Multiple Column Range.    Cell E4 formula: Determines the MAX value of each row in the 2 column range A2:B10, and returns the SUM of each of these MAX values.      ...

## SUM Cell values containing any of the Multiple Values in Full or in Part

SUM Cell values containing any of the Multiple Values in Full or in Part     SUMIF – SUM corresponding column B cells where column A cells contain any of the Multiple Values (C2:C5) in Full or in Part.    Cell E8 formula: Return SUM of corresponding column B cells, where column A cells contain any...

## SUM of – Last 12 values in a Column, Largest 5 out of Last 12 values, last 12 values Excluding Highest / Lowest

SUM of – Last 12 values in a Column, Largest 5 out of Last 12 values, last 12 values Excluding Highest / Lowest     SUM of Last 12 values in a Column; Sum Largest 5 out of Last 12 values; SUM last 12 values Excluding Highest / Lowest values.    Cell F3 formula: Return SUM...

## SUM Non-Contiguous Ranges of a Column – SUM EACH block of consecutive cells

SUM Non-Contiguous Ranges of a Column – SUM EACH block of consecutive cells, starting from specific position, & skip specific number of cells     SUM EACH of the Non-Contiguous Blocks of consecutive number of cells, with a specified start position,  after skipping specific number of consecutive cells    Cell D2 formula: In consecutive cells of...

## Assign separate Values to Non-Contiguous Cells and return SUM

SUM – Assign separate Values to Non-Contiguous Cells and return SUM     SUM – Assign separate Values to Non-Contiguous Cells in a column, and return SUM    Cell E4 formula: Check ALL cells in the range A2:A10 for “Yes” & return SUM: if 2nd or 8th cells are “Yes” add 10 for each; if any other...

## SUM Largest 4 Values in Non-Contiguous Cells of a Row

SUM Largest 4 Values in Non-Contiguous Cells of a Row     SUM Largest 4 Values in Non-Contiguous Cells of a Row    Cell B9 formula: Non-Contiguous cells with even intervals – SUM largest 4 values in the 2nd row cells of B2, F2, J2, N2, R2 & V2 (consider 1st cell, skip the next 3 cells). Returns...

Scroll to top