+ Reply to Thread
Results 1 to 4 of 4

Search for 2 values, 1st one then the other both must match, and return a total count.

  1. #1
    Registered User
    Join Date
    02-07-2016
    Location
    Colorado, USA
    MS-Off Ver
    2010, 2013, 2016, 365
    Posts
    2

    Question Search for 2 values, 1st one then the other both must match, and return a total count.

    Hello and Thank you for entertaining my question/problem.

    I am trying to write a formula in a cell, that reads a range of data in one column, then read data in each row, and if it finds two matching specified values, it will look at count each occurrences, and return with a total count in a separate cell. So below is the sample data, and A7 is where I want the information, which is =X:

    Range 1: A1:A4
    Range 2: each corresponding row
    Search for Value in Range 1: "AB"
    Search for Value in Range 2: "400"
    If search in Range 1 is a match, then search in that row in Column B, if that row matches Range 2 value, count 1 for each occurrence.
    Returned Result in A7: 2

    A B
    1 Noun Number
    2 AB 400 <--Match
    3 AB 100
    4 SRA 400
    5 SRA 200
    6 AB 400 <-- Match
    7 2

    Thank you again. I'm thinking I might have to brush up on some VBA, but I like to avoid it, if possible.

    (I am working with Excel 2012, which the expectation of upgrading to 2016 (at work). However, I use Office 365 on the go and 2016 at home.)

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Search for 2 values, 1st one then the other both must match, and return a total count.

    With your sample data:
    A
    B
    1
    Noun
    Number
    2
    AB
    400
    3
    AB
    100
    4
    SRA
    400
    5
    SRA
    200
    6
    AB
    400
    This formula returns the count of rows where Col_A = "AB" and Col_B = 400
    Please Login or Register  to view this content.
    In that example, the formula returns: 2

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    02-07-2016
    Location
    Colorado, USA
    MS-Off Ver
    2010, 2013, 2016, 365
    Posts
    2

    Re: Search for 2 values, 1st one then the other both must match, and return a total count.

    Yes! Countifs (Forgot about ifs.) Thank you Ron! Much appreciated!

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Search for 2 values, 1st one then the other both must match, and return a total count.

    Glad you got something you can use!

    If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)

+ 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] Using INDEX MATCH to return unique values for non-unique search term
    By rico_suave in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-03-2015, 01:53 AM
  2. Replies: 6
    Last Post: 03-12-2015, 01:35 AM
  3. [SOLVED] Return Multiple values from a column with index and match, and search criteria
    By marcusduton in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 02-15-2015, 09:59 PM
  4. Is there a formula that will search for nonspecific values and return a match?
    By Yodaman1997 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-23-2014, 05:03 PM
  5. Replies: 7
    Last Post: 08-04-2013, 03:41 PM
  6. [SOLVED] How to return multiple values that match a single search criteria?
    By JSallen in forum Excel General
    Replies: 4
    Last Post: 11-28-2012, 11:49 AM
  7. How to Search, Count, Match and Post Values
    By vincentws in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-17-2006, 12:20 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