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

Data Analysis

  • How To Create Frequency Distribution in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Working With Tables in Excel
  • Conditional Formatting Data bars Examples in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel

References

  • Vlookup Examples in Excel
  • To count total rows in a range in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Count rows with at least n matching values
  • How to use Excel COLUMN Function

Data Validations

  • Excel Data validation must begin with
  • Excel Data validation only dates between
  • Excel Data validation with conditional list
  • Excel Data validation specific characters only
  • Excel Data validation must contain specific text

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.

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

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:

{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 CHOOSE Function

Next Post:

Customize Ribbon 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

  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • How to use IFS function in Excel
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function example in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to enter Today’s Date or Static Date and Time in Excel
  • How to calculate future date say 6 months ahead in Excel
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • Count times in a specific range in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • Calculate conditional mode with criteria in Excel
  • Map text to numbers in Excel
  • Map inputs to arbitrary values in Excel
  • Group times into 3 hour buckets in Excel

General

  • Convert column letter to number in Excel
  • Basic text sort formula in Excel
  • How to calculate profit margin percentage in Excel
  • How to get amount with percentage in Excel
  • Lock Cells in a Worksheet Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning