Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to count table rows in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to create a Histogram in Excel
  • How To Insert and Customize Sparklines in Excel
  • Data Series in Excel

References

  • How to get last row in mixed data with blanks in Excel
  • Perform case-sensitive Lookup in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in numeric data in Excel
  • How to get first row number in range in Excel

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation no punctuation
  • Excel Data validation date in specific year
  • Excel Data validation specific characters only
  • Excel Data validation whole percentage only

Count multiple criteria with NOT logic in Excel

by

This tutorial shows how to Count multiple criteria with NOT logic in Excel using the example below;

Formula

=SUMPRODUCT((range1=criteria1)*ISNA(MATCH(range2,criteria2,0)))

Explanation

To count with multiple criteria, including logic for NOT one of several things, you can use the SUMPRODUCT function together with the MATCH and ISNA functions.

In the example shown, the formula in G8 is:

=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))

Where “gender” is the named range C4:C12, and “group” is the named range D4:D12.

Worked Example:   Count dates in given year in Excel

Note: MATCH and ISNA allow the formula to scale easily to handle more exclusions, since you can easily expand the range to include additional “NOT” values.

How this formula works

The first expression inside of SUMPRODUCTS tests values in column C, Gender, against the value in F4, “Male”:

(gender=F4)

The result is an array of TRUE FALSE values like this:

{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}

Where TRUE corresponds to “Male”.

Worked Example:   Sum if equal to one of many things in Excel

The second expression inside of SUMPRODUCTS tests values in column D, Group, against the values in G4:G5, “A” and “B”. This test is handled with MATCH and ISNA like this:

ISNA(MATCH(group,G4:G5,0))

The MATCH function is used to match every value in the named range “group” against values in G4:G5, “A” and “B”. Where the match succeeds, MATCH returns a number. Where the MATCH fails, MATCH returns #N/A. The result is an array like this:

Worked Example:   How to check cell that contains one of many with exclusions in Excel

{1;2;#N/A;1;2;#N/A;1;2;#N/A}

Since #N/A values correspond to “not A or B”, ISNA is used to “reverse” the array to:

{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

Now TRUE corresponds to “not A or B”.

Inside SUMPRODUCT, the two array results are multiplied together, which creates a single numeric array inside SUMPRODUCT:

SUMPRODUCT({0;0;1;0;0;1;0;0;0})

SUMPRODUCT then returns the sum, 2, representing “2 males not in group A or B”.

Post navigation

Previous Post:

How to use Excel FALSE Function

Next Post:

Large with criteria in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • OR function Examples in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel AND Function

Date Time

  • Count dates in current month in Excel
  • Convert Unix time stamp to Excel date
  • How to calculate percent of year complete in Excel
  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • Convert decimal seconds to Excel time

Grouping

  • Group arbitrary text values in Excel
  • Categorize text with keywords in Excel
  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel
  • Calculate conditional mode with criteria in Excel

General

  • Count cells that do not contain errors in Excel
  • How to Insert Cells, Row and Rows in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • 231 Keyboard Shortcut Keys In Excel
  • Spell Check in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning