+ Reply to Thread
Results 1 to 3 of 3

Countifs Multiple criteria for single column

  1. #1
    Registered User
    Join Date
    02-01-2021
    Location
    Phoenix, Arizona
    MS-Off Ver
    Office 365
    Posts
    6

    Post Countifs Multiple criteria for single column

    Hi there. I am trying to write a Countifs formula where two of the columns have multiple criteria, but I can't seem to get it to work. This is what I have gotten to work so far:

    =COUNTIFS('Jan21'!$A:$A,$B5,'Jan21'!$H:$H,"DOM",'Jan21'!$L:$L,1) - this will return the correct number (7)

    but I need column H to be include either DOM or INTL, and L to count 1-21 inclusive. I could write it as

    =COUNTIFS('Jan21'!$A:$A,$B5,'Jan21'!$H:$H,"DOM",'Jan21'!$L:$L,1)+COUNTIFS('Jan21'!$A:$A,$B5,'Jan21'!$H:$H,"INTL",'Jan21'!$L:$L,1)+COUNTIFS('Jan21'!$A:$A,$B5,'Jan21'!$H:$H,"DOM",'Jan21'!$L:$L,2)+COUNTIFS('Jan21'!$A:$A,$B5,'Jan21'!$H:$H,"INTL",'Jan21'!$L:$L,2)

    etc, all the way to 21, but that would be a horribly long formula with 42 parts. Is there a condensed way of writing this?

    Many thanks.
    Last edited by JoelleM92; 02-04-2021 at 05:14 PM.

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,899

    Re: Countifs Multiple criteria for single column

    Try this:

    =Sum(COUNTIFS('Jan21'!$A:$A,$B5,'Jan21'!$H:$H,{"DOM","INTL"},'Jan21'!$L:$L,{1;2}))

  3. #3
    Registered User
    Join Date
    02-01-2021
    Location
    Phoenix, Arizona
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Countifs Multiple criteria for single column

    This is perfect, thanks! I had tried using the {} format before, but I was doing something wrong and it didn't work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Countifs multiple criteria- criteria if cell is greater than another i adjacent column
    By Sircool1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2018, 11:23 AM
  2. [SOLVED] Countifs with multiple criteria and ranges within single column?
    By Skiptomylou in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-13-2018, 12:56 PM
  3. Countifs formula with multiple criteria against a single range
    By GymnasticHippo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2017, 09:54 PM
  4. Countifs with multiple criteria in single criteria range
    By SUHAS KARHADKAR in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 12-01-2016, 10:55 AM
  5. Countifs with multiple criteria in single criteria range
    By MCP313 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2016, 03:51 AM
  6. [SOLVED] COUNTIFS: Using { } to search for multiple values in single column
    By jeversf in forum Excel General
    Replies: 3
    Last Post: 04-03-2012, 07:53 AM
  7. Countifs with multiple criteria in a single range
    By Wolfpackfan320 in forum Excel General
    Replies: 1
    Last Post: 02-27-2012, 04:54 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1