+ Reply to Thread
Results 1 to 2 of 2

Counting with a referenced "or" criteria

  1. #1
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    24

    Counting with a referenced "or" criteria

    Hi all,

    I'd like to count a simple table, based on 2 criteria:

    1) If column "C" contains the letter "X"
    2) If column "H" contains EITHER text entered into U2, or U3.

    For other formulas that didn't require the "OR" condition, I've used either of these:
    =COUNTIFS($H:$H,$U$2,$C:$C,"X*")
    =SUMPRODUCT(--($H:$H=$U$2),--ISNUMBER(SEARCH("X",C:C,1)))

    I also know there's the option of adding formulas together for each "OR" condition, such as:
    =COUNTIFS($H:$H,$U$2,$C:$C,"X*")+COUNTIFS($H:$H,$U$3,$C:$C,"X*")

    Is there a condensed formula (either countifs or sumproduct) that can count based on a referenced OR statement? The following doesn't work, but it's what I conceptually started with:
    =COUNTIFS($H:$H,{"$U$2","$U$3"},$C:$C,"X*")

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Counting with a referenced "or" criteria

    Try

    =SUMPRODUCT(COUNTIFS($C:$C,"X*",$H:$H,$U$2:$U$3))

+ 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. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  3. Replies: 3
    Last Post: 04-16-2014, 10:00 AM
  4. [SOLVED] Counting data only contains text (ignore mark "-" & "")
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-12-2014, 09:45 PM
  5. [SOLVED] Find multiple "text" criteria and return as ""Yes" in Matrix
    By bertrand82 in forum Excel General
    Replies: 11
    Last Post: 04-30-2012, 09:20 AM
  6. Performing "SUMIF" where "range" & criteria" are texts
    By joseedua in forum Excel General
    Replies: 1
    Last Post: 02-11-2010, 01:09 PM
  7. Replies: 0
    Last Post: 07-09-2009, 04:07 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