+ Reply to Thread
Results 1 to 3 of 3

Countifs - Ignoring irrelevant data

  1. #1
    Forum Contributor
    Join Date
    12-13-2011
    Location
    Yorkshire, UK
    MS-Off Ver
    Office365
    Posts
    183

    Countifs - Ignoring irrelevant data

    Hi,

    I may be struggling with a first day back at work brain but I'm struggling to think of how to structure a formula.

    What I need to do (Using the example attached) is count all the CAT3s but only from sites A,B and C. I need it to ignore D,E and F.

    If I was doing it for a single site then it would just be

    =COUNTIFS(B3:B25,"Site A",C3:C25,"CAT3")

    But I want to widen the criteria in column B but ignore parts thereof.

    This is just test example data and part of a larger formula but I'm struggling at the first part!
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,213

    Re: Countifs - Ignoring irrelevant data

    Please try

    =SUMPRODUCT(COUNTIFS(B3:B25,{"Site A","Site B","Site C"},C3:C25,"CAT3"))

    or A,B,C is less than D

    =COUNTIFS(B3:B25,"<Site D",C3:C25,"CAT3")
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    12-13-2011
    Location
    Yorkshire, UK
    MS-Off Ver
    Office365
    Posts
    183

    Re: Countifs - Ignoring irrelevant data

    Thanks, the first part worked a treat and I was able to include it the rest of the formula to work fine.

+ 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(?) Ignoring Duplicates
    By thesonofdarwin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-02-2017, 12:31 AM
  2. Countifs function, ignoring those blanks
    By stevend1978 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-19-2015, 06:33 PM
  3. Replies: 1
    Last Post: 11-02-2014, 03:42 PM
  4. [SOLVED] ignoring duplicates in countifs
    By banvir1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2013, 01:21 PM
  5. [SOLVED] AutoFliter and remove irrelevant data within a cell
    By russkris in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-26-2012, 01:02 AM
  6. [SOLVED] Countifs() ignoring FALSE?
    By y_t in forum Excel General
    Replies: 1
    Last Post: 05-25-2012, 08:53 PM
  7. Match lists + delete irrelevant data
    By Smurg in forum Excel General
    Replies: 5
    Last Post: 11-29-2011, 11:21 AM

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