+ Reply to Thread
Results 1 to 2 of 2

How can I run CountIfs + CountIfs without double counting?

  1. #1
    Registered User
    Join Date
    02-06-2017
    Location
    SF, California
    MS-Off Ver
    Excel 2007
    Posts
    1

    How can I run CountIfs + CountIfs without double counting?

    I'm trying to analyze the frequency of how often topics show up through a data set of open text feedback by doing a count of the number of times keywords have shown up. So, for example:

    FEEDBACK 1: I liked XYZ because of TRAIT #1 HERE and TRAIT #2 HERE.
    FEEDBACK 2: I hated JKL because of NEGATIVE TRAIT #1.

    Topic 1 keywords: XYZ, Trait #1 Here, Trait #2 Here
    Topic 2 keywords: JKL, Negative Trait #1

    So the way I currently am doing it is:

    Evaluating Topic 1: Counting if XYZ appears + Counting if TRAIT #1 appears + TRAIT #2

    However, I realized that this leads to double counting. So, following this example, if I evaluated the two example cell, my formula would then output "3" for Topic 1, even though it was only a single feedback post that had topic one in it!! Is there anyway I can manipulate my formula to only indicate "1" for a single cell even if multiple keywords appear in it? So something essentially that makes my search go for "Summate if XYZ OR TRAIT #1 HERE OR TRAIT #2 HERE" is there instead of the "And".

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,461

    Re: How can I run CountIfs + CountIfs without double counting?

    One way would be to have 4 helper columns. The first would count phrase 1; the second, phrase 2; the third, phrase 3. The fourth would be set to 1 if ANY of the other helper columns is greater than or equal to 1 (use OR for this). Then you can total each phrase and total the rows with any combination.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ 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. COUNTIFS avoiding double counting
    By nbest in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-28-2014, 07:22 PM
  2. [SOLVED] COUNTIFS is double counting
    By infiniticihr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-22-2014, 01:09 PM
  3. Countifs and Double Counting
    By jlacsina in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-18-2013, 09:55 PM
  4. Double counting based on COUNTIFS with OR statement
    By figo12 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-10-2013, 12:12 PM
  5. COUNTIFS OR...multiple countifs without duplication in the numbers
    By HooligaD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2012, 09:53 AM
  6. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  7. Countifs not counting, returns #value
    By Qualo_Jinn in forum Excel General
    Replies: 3
    Last Post: 12-09-2011, 04:13 PM

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