Tag: Excel Ebook
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...
Extract a Sorted List of Unique values in a column
Return Sorted Unique List of Values from Column having only Text or only Numbers Sort Column values in Ascending / Descending Order, Retain Duplicates or Extract only Once (ie. Uniques) Column F: Extracts Uniques & Sorts column E values in Ascending order – column E can have ONLY Numbers. Enter Array formula...
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...
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...
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...
Rank & Sort Column Values, Ignoring Error Values
Rank & Sort Numbers in a Column, Ignoring Error Values Rank & Sort Column Numbers, Skipping Error Values Column B: Ranks column A values, Ignoring Error values, in Descending Order (high to low). Column A contains numbers, and also error values (#N/A, #DIV/0!, etc), in random order. Enter Array formula (Ctrl+Shift+Enter) in cell...
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...
Split Values into Multiple Equal Groups
Split Values into Multiple Equal Groups / Ranks Assign Column Values into Groups, with Group 1 having Largest Values & Group 5 Lowest Values Column Values are divided equally between Groups, each group is ranked as per column values, Group 1 having Largest Values & Group 5 Lowest Values. Column...
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...
Count Unique values in a Column, with Conditions
Count Unique values in a Column, with Condition(s) Count Unique values in a Column, with Condition(s) – each distinct value is counted only once irrespective whether it appears once or multiple times Column A contains values (text or numbers) which occur either only once or multiple times (Duplicate). Columns B and C...
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 Uniques Appearing in Only One or in Both Columns
Count Uniques appearing in BOTH Columns or in 1 Column and NOT in Other Count Uniques: (i) which appear in BOTH Columns; (ii) which appear in 1 Column and NOT in the Other Column Each Student (column A) has Multiple Subjects in Semester 1 (column B) and in Semester 2 (Column C)....
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...
Count Once for Occurrence of a Value in all columns of Same Row
Count Occurrences in one or both Columns, consider Once if Occurrence in Same Row of 2 columns Count Occurrences of a Value in Whole or in Part: (i) Count Once if Occurrence in Same Row of One or Both columns or (ii) Count Twice if Occurrence in Both Columns Column E COUNTS Occurrences...
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 Uniques, Condition in One or Both Columns
Count Values Once (Unique) or as many times they Occur, Condition in Either 1 or Both Columns Count Values where Multiple Occurrences are Counted Once (Unique values) for Condition of “Yes” in Either 1 or Both Columns Cell E4: Count number of Unique Names in column A (Multiple Occurrences are Counted Once), if Either...
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...
Count Unique Values matching any one of the Multiple values
Count Unique Values where other column values Match ANY of the Array values Count Unique column A values (Multiple Occurrences of a Value are Counted Once) where column B values Match ANY of the Array values (ie. Array Criteria Match) Column G formulas COUNT Unique column A values where column B values Match...
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...
Unique List from Single Column, Skip Specific Values
Extract Unique List from a Single Column (Multiple Occurrences Appear only Once) – Skip Specific Value(s) Return Unique List of Values from a Single Column, skipping Specific Values Column A values appear either once (Unique) or multiple times (Duplicate) – these are extracted to appear ONLY ONCE, Skipping Specified Value(s). In...
Count Unique Names, where Name corresponds to Multiple Values
Count Unique Names, where each Name corresponds to Multiple Values Count Unique Names (Multiple Occurrences of a Name are Counted as One) in column, where each Name corresponds to specific Multiple Values Cell F6: Count Unique Names in column A, having corresponding column B values of both cells E2:E3 (ie. “ABC” & “KLM”)....
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...
Unique List Appearing in One Column and Not in Other
Return Unique List of values which appear only in One Column and NOT the other – or Unique List from Both Columns Return Unique List of Values from a Column which do not appear in the Other Column Column D: Extract Unique List of values from column A (each column A value...
Count Uniques, Multiple AND / OR Conditions
Count Unique values in a column, subject to Multiple Conditions – AND / OR Count Unique values in column A subject to multiple AND/OR conditions Cell J2 formula – Count Unique values in column A subject to multiple AND/OR conditions: (i) If ANY column B, D or F has “Y” (ie. if atleast...
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...
Unique List, Sorted in Ascending Order, Appearing in One Column and Not in Other
Return Unique List of values, Sorted in Ascending Order, which appear only in One Column and NOT the other Return Unique List of values, Sorted in Ascending Order, which appear only in One Column and NOT the other Column D: List column A values which do not appear in column B, duly sorted...
Count Rows containing Multiple specified Values
Count Number of Rows in a Range where Multiple Values appear atleast once in the Same Row Count Number of Rows, wherein Multiple Specified Values appear in a Row Cell K3 formula Counts Number of Rows in a Range where Multiple Values appear atleast once in the Same Row. Cell K3: Number of rows...
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 ...
Extract Unique sub-strings of 1st 5 Characters from each column cell
Extract Unique List from a Column: (i) Fixed Length of 1st 5 Characters of each cell string; or (ii) Variable Length of Characters in each cell Preceding the 1st “-” (Dash) Extract a Unique List of Codes from column cells: (i) Fixed Length of 1st 5 Characters; (ii) Variable Number of Characters...
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...
Extract Values which appear ONLY once, from a Column
Extract Values which appear ONLY once (ie. Unique Values) from a Column Only those values which appear ONCE in a Column are extracted. Column A values appear either once (Unique) or multiple times (Duplicate) – only those values which appear ONCE are extracted in columns B; Enter Non-Array formula in...
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...