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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Scroll to top