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

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

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

Scroll to top