+ Reply to Thread
Results 1 to 9 of 9

Avoid Double Counting - Ignore Text in Criteria

  1. #1
    Registered User
    Join Date
    06-01-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    20

    Avoid Double Counting - Ignore Text in Criteria

    Hi All,

    I'm struggling again with the excel logic to perform something that feels simple, it's frustrating!

    Ok here some sample data:

    avoid double counting.jpg

    I'm trying to create a formula that checks an ID and returns TRUE if a type other than "apple" or "banana" is there. The problem I'm having is double counting so if I write something like:

    =IF(SUM(--(COUNTIFS($A$2:$A$15,C2,$B$2:$B$15,{"<>apple","<>banana"})>0))=2,TRUE," ")

    it doesn't work because when counting excluding "apple" it counts "banana" and vice versa.

    I want to do something with OR or AND, but that doesn't work or I can figure it out.

    Help greatly appreciated!

    Thank you
    Last edited by SamFitz; 09-29-2016 at 09:26 AM.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Avoid Double Counting - Ignore Text in Criteria

    Try this...

    Data Range
    A
    B
    C
    D
    1
    ID
    Type
    ID
    2
    1
    apple
    1
    TRUE
    3
    1
    banana
    2
    TRUE
    4
    1
    pear
    3
    5
    2
    apple
    4
    TRUE
    6
    2
    mango
    5
    7
    2
    beetroot
    8
    3
    apple
    9
    4
    apple
    10
    4
    banana
    11
    4
    beetroot
    12
    5
    apple
    13
    5
    apple
    14
    5
    banana
    15
    5
    banana
    16
    ------
    ------
    ------
    ------


    This formula entered in D2 and copied down:

    =IF(SUMPRODUCT(--(A$2:A$15=C2),--ISNA(MATCH(B$2:B$15,{"Apple","Banana"},0))),TRUE,"")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    09-27-2016
    Location
    Wakefield, England
    MS-Off Ver
    2013
    Posts
    3

    Re: Avoid Double Counting - Ignore Text in Criteria

    You could use the below in D2 and drag down:

    =IF(COUNTIFS(A:A,C2,B:B,"<>Apple",B:B,"<>Banana")>0,TRUE,"")

    Making the assumption you want to return blank if there is only Apple or Banana there?

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

    Re: Avoid Double Counting - Ignore Text in Criteria

    I think this is a continuation of this
    http://www.excelforum.com/excel-form...-function.html

    That's OK, this is the opposite now, but it's just helpful to know where this is coming from.

    Can you explain the logic you're tyring to achieve here?
    If you already have a formula that is getting the ones that DO = Apple or Banana
    Wouldn't the answer here just be the overall sum MINUS that ?

    So say F2 has this formula
    =IF(SUM(--(COUNTIFS($A$2:$A$11,D2,$B$2:$B$11,{"apple","banana"})>0))=2,SUM(SUMIFS(C$2:C$11,A$2:A$11,D2,B$2:B$11,{"apple","banana"})),0)

    Wouldn't you just need this
    =SUMIFS(C$2:C$11,A$2:A$11,D2)-F2

  5. #5
    Registered User
    Join Date
    06-01-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    20

    Re: Avoid Double Counting - Ignore Text in Criteria

    Thank you Jonmo1, Richard and Tony,

    I'm not in the office until later today to try out the formulas but I wanted to say thank you for replying and to give a bit more background.

    The aim is a little different to the previous thread, I simply want to identify those IDs which include "apple" plus one other type that isn't "banana". Essentially just ignore "banana", it doesn't matter if it is present in an ID or not.

    So if an ID includes only "apple" or multiple "apple" types, I want a false/blank result. If it includes only "apple" and "banana", or multiples of, then again I want a false/blank result.

    "apple" + AN OTHER (not "banana") = True

    I hope that helps to make things clear, I really appreciate all the advice and look forward to testing out the formulas later!

    Thank you

  6. #6
    Registered User
    Join Date
    06-01-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    20

    Re: Avoid Double Counting - Ignore Text in Criteria

    Thank you all very much, all your formulas work to the question I asked but I apologise because I've not asked the right question, I'm really sorry. My 5th post is correct, my first isn't - For a TRUE result I would like "apple" to be present + one other type that isn't "banana". Some examples:

    TRUE: apple, banana, beetroot
    TRUE: apple, apple, banana, pear
    TRUE: apple, pear
    FALSE/BLANK: apple
    FALSE/BLANK: apple, banana
    FALSE/BLANK: apple, apple, banana, banana
    FALSE/BLANK: beetroot, pear
    FALSE/BLANK: banana, beetroot

    I'm sorry again. I will play around with the formulas you've created to see if I can get something to work.

    Thank you
    Last edited by SamFitz; 09-29-2016 at 08:32 AM.

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

    Re: Avoid Double Counting - Ignore Text in Criteria

    So you want a true/false if a specific ID has at least 1 that is Apple AND at least one other that is NOT Apple or Banana ?

    Try

    =AND(COUNTIFS($A$2:$A$11,D2,$B$2:$B$11,"apple")>0,COUNTIFS($A$2:$A$11,D2,$B$2:$B$11,"<>apple",$B$2:$B$11,"<>banana")>0)

  8. #8
    Registered User
    Join Date
    06-01-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    20

    Re: Avoid Double Counting - Ignore Text in Criteria

    Exactly! and yes that works, thank you!!! of course! I was looking at trying to do something with AND but I hadn't considered putting a formula within it, I was just testing for specific text.

    Aaargh! It so easy. I'm annoyed I couldn't think of that.

    *Tweaking the formula slightly to make it work with the example in this thread:

    =AND(COUNTIFS($A$2:$A$15,C2,$B$2:$B$15,"apple")>0,COUNTIFS($A$2:$A$15,C2,$B$2:$B$15,"<>apple",$B$2:$B$15,"<>banana")>0)

    Thank you so much AGAIN!

    I really hope to get better at these
    Last edited by SamFitz; 09-29-2016 at 09:27 AM.

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

    Re: Avoid Double Counting - Ignore Text in Criteria

    You're welcome.

+ 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] Text trimmer double criteria
    By makinmomb in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-15-2015, 03:10 PM
  2. [SOLVED] Avoid double counting of same value
    By Karen57 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-02-2015, 09:23 AM
  3. How to avoid double-counting inventory after it has been accounted for?
    By excelrookie2014 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-05-2014, 07:37 PM
  4. VBA combo boxes (ignore blanks + avoid duplicates)
    By pbobadilla in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2012, 05:44 PM
  5. [SOLVED] how to avoid null/double click
    By var in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2012, 09:55 AM
  6. Need help on counting text within certain criteria
    By christinac in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-27-2006, 08:15 AM
  7. Counting text criteria
    By Belinda in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-20-2005, 06:05 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