+ Reply to Thread
Results 1 to 2 of 2

Formula to identify multiples with 2 criteria

  1. #1
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Formula to identify multiples with 2 criteria

    Hi, i am wondering what the excel formula would be if I need to identify if there are multiple items in column A that also have multiple items in column B.

    An example is attached.

    In this case, I would want to know that Example 3 has 3 Grapes and also that Example 5 has two pineapples
    Attached Files Attached Files

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Formula to identify multiples with 2 criteria

    Without knowing anything further, I would throw the data into a Pivot Table, stick, say, the "Fruits" in the "Column Label" and "Values (Count of)" fields and the other,say the "Examples", in the "Row Labels" field.

    That will create a 2D matrix that will display the count of overlaps.

    Because,
    The request as it stands has kind of three parts: one is the combinatorics of assessing what all the combinations are, two is counting up the instances at each combination, and three is displaying the results in a useful manner.

    Another option:
    Please Login or Register  to view this content.
    Then pull down & filter column C for >1, or slap the ">1" on the end of the equation and filter for TRUE; both of which would display the full count of rows, so all three "Example 3" & "Grape" combos.

    (In either case you would be well-served to insert a header row above the data).

    But I think the PT is probably going to be more intuitively informative and probably more robust.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

+ 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: 1
    Last Post: 09-12-2014, 10:38 AM
  2. [SOLVED] Formula to identify data type according to criteria list
    By Billaus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-30-2014, 11:47 AM
  3. Alternative Formula for multiples IFs (to make my formula shorter)
    By chico.corrales in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-07-2013, 12:37 AM
  4. creating formula for multiples of 2 or 3
    By ajcrew in forum Excel General
    Replies: 1
    Last Post: 05-29-2012, 10:10 AM
  5. Count multiples within conditional formula?
    By Nate Westcott in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-31-2011, 04:50 PM
  6. Counting multiples with criteria
    By vancoservices in forum Excel General
    Replies: 16
    Last Post: 08-12-2010, 09:08 AM
  7. Multiples of a # formula
    By Alpha Zero in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-13-2008, 07:37 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