Count Maximum Number of Consecutive Occurrences of a Value

  Count Maximum Number of Consecutive Occurrences of a value in a Column     Count Maximum Number of Consecutive Occurrences of a value in a Column    Cell C3 formula Counts Maximum Number of Consecutive Occurrences, of a value in a Column – Maximum Consecutive Occurrences of the number 5 (cell B3 value) in column A.    ...

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...

Extract Text After Last Occurrence of a Number in a String

  Extract Sub-String After Last Occurrence of a Number / Numerical in a String     Extract All Characters starting After the Last occurrence of a Number (ie. 0 to 9) till the end.    Cell B2 formula: Extracts all Text starting After and to the Right of the Last occurrence of a Number (ie. 0 to...

Count Groups of Consecutive Positive Values

  Count Groups of Consecutive Positive or Negative or Zero Values     Count Groups of Consecutive Positive Values    Column C formulas Count Groups of Consecutive Positive / Negative / Zero Values; Average (or Maximum) Number of Cells in Positive / Negative / Zero Value Groups.   Cell C3 Formula: Count Groups of Consecutive Positive Values 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.      ...

Extract the Number Preceding Specific Text in a String

  Extract the Number immediately Preceding Specific Text in a String     Extract the “Number” or “Alphanumeric Sub-string”, immediately Preceding Occurrence of a specific text (“no”) within a String.    Cell C2 formula: Extract the “Number” or “Alphanumeric Sub-string”, immediately Preceding Occurrence of the Text “no” in cell A2 string.      ———————————————————————————————– Extract Number Before Specific...

Count Continuous Non-Blank cells which appear Last

  Count Number of Continuous Non-Blank cells which appear Last     Count the number of Continuous Non-Blank cells which occur last in the row (ie. the last group of non-blank cells)    Column A & B formulas Count the number of Continuous Non-Blank cells which occur last in the row (ie. the last group of non-blank...

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...

Count Rows with occurrence of atleast one number

  Count Number of Rows with occurrence of atleast one 1     Count Number of Rows in a Multi Column-Row Array, having occurrence of atleast one 1    If a row in the range A2:F20 contains atleast a single 1, it is considered and counted. Even if a row contains multiple 1s, it is counted as...

Position of Last Numeric in a String

  Position of Last Occurrence of a Numeric in a cell string     Position of 1st or Last Numeric character in an AlphaNumeric String.    Cell E2 formula: Returns the Position of Last Numeric in Cell A2 String.    ———————————————————————————————–   Position of Last Numeric in a String    Cell E2 formula: Returns the Position of 1st Numeric character...

Position of First Text Character in a String

  Position of 1st Occurrence of a Text Character in cell string     Position in AlphaNumeric String of 1st Text Character, Last Text Character, 1st Letter (A-Z), Last Digit Preceding 1st Text Character.    Cell B2 formula: Returns the Position of 1st Text (Non-Numerical) Character in cell A2 String.    ———————————————————————————————–  Position of 1st Text...

Count Multiple Occurrences of Array Values

  Count Multiple Occurrences of Array Values in a Range     Count Multiple Occurrences of Array Values in a Range    Cell E11 formula: Counts Occurrences of column B values in column A (Unique column B values are counted as many times each appears in column A). Unique column B values are counted as many times...

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...

Count number of Cells containing Specific Letter(s)

Count the number of Cells containing Specific Letters / Characters 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 the number of Cells containing Specific Letter(s)...

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...

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...

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...

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...

Countif Array Criteria, Multiple Columns, AND / OR conditions

COUNTIF with Array Criteria, Multiple Columns, AND / OR conditions 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. COUNTIF (or Sumproduct) for Multiple Columns, Array Criteria, using...

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...

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. ...

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 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...

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...

Split Names – Extract First, Middle & Last Names in Separate Cells

  Split Names – Extract First, Middle & Last Names in Separate Cells     Extract First Name, Middle Name & Last Name(s) in separate cells, from Full Name, where each name is separated with a Space. 2nd cell to contain the excess Middle Names, where there are more than 3 Names separated with Space    Cell...

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...

Extract Text after Last Blank Space in a String

  Extract all Text after Last Blank Space in a cell String     Extract Text after Last Blank Space in a String.    Cell D12 formula: Cell D12 formula Extracts all Text after last blank space in cell A12 string.      ———————————————————————————————–  Extract Text After Last Space   Cell D12 formula: Extracts all Text after last blank space in...

Position of First Number in a String

  Position of 1st Occurrence of a Number in a cell string     Position of 1st Number in String; Extract String to the Left of 1st Number / Starting from 1st Number.    Cell C2 formula: Returns the Position of First Number in Cell A2 String.    ———————————————————————————————–  Position of 1st Number in String    ...

Find ‘Smallest’, ‘Largest’, ‘K-th Smallest’ and ‘K-th Largest’ Numbers in a Range

Find ‘Smallest’, ‘Largest’, ‘K-th Smallest’ and ‘K-th Largest’ Numbers in a Range with Excel Functions   Related Links: Remove Duplicates in a range, using “Find Smallest and Largest Value with VBA. Using ParamArray to find Minimum Value.   —————————————————————————————————————————  Find Smallest Number or K-th Smallest Number in a Range Find Smallest Number in a Range,...

Scroll to top