+ Reply to Thread
Results 1 to 5 of 5

Array formula with multiple match criteria to count exact phrase within a string of text

Hybrid View

LDHall Array formula with multiple... 04-21-2020, 05:23 AM
BrianM45 Re: Array formula with... 04-21-2020, 06:19 AM
LDHall Re: Array formula with... 04-21-2020, 07:03 AM
XOR LX Re: Array formula with... 04-21-2020, 07:28 AM
LDHall Re: Array formula with... 04-21-2020, 07:32 AM
  1. 04-21-2020, 05:23 AM #1
    LDHall is offline
    Registered User
    Join Date
    04-03-2020
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    12

    Array formula with multiple match criteria to count exact phrase within a string of text

    Hi everyone,

    I posted a couple of weeks ago about a similar array formula that I needed to count an exact phrase within a string of text, but less complicated.

    My spreadsheet has developed slightly since then.

    CaptureA.PNG

    In cell G3 I now need a formula that will count how many times the exact string of text in cell G2 appears in cells D2:D21, but specifically only for the venue named in cell F3 and the exam paper named in cell F2. This formula then needs to be applied to populate the rest of the table for Exam 1. I then need to apply this formula in the two tables for Exam 2 and Exam 3. The overall goal is to quickly identify which questions candidates are struggling with the most on each exam and to see if there are any common weaknesses between exam venues.

    Any help would be greatly appreciated, thank you.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-21-2020, 06:19 AM #2
    BrianM45 is offline
    Forum Contributor
    Join Date
    05-10-2012
    Location
    Paris, France
    MS-Off Ver
    2016/365
    Posts
    123

    Lightbulb Re: Array formula with multiple match criteria to count exact phrase within a string of te

    Hi LDHall

    Here is the file with the formula that uses the function : NB.SI.ENS() in French / COUNTIFS() in English

    Regards
    Attached Files Attached Files
    Last edited by BrianM45; 04-21-2020 at 06:27 AM.
    If you are satisfied with my answer, please add reputation
    Reply With QuoteRegister To Reply
  3. 04-21-2020, 07:03 AM #3
    LDHall is offline
    Registered User
    Join Date
    04-03-2020
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    12

    Re: Array formula with multiple match criteria to count exact phrase within a string of te

    Thank you for such a quick response! On reflection, the formula partly works but not fully. For example, for Exam 2 Venue A Q1 it is returning the value 1. However, this isn't correct as nobody submitted Q1 as a weakness. I think your formula finds the 10 and counts that as an entry for Q1.

    Any ideas how to fix this?
    Last edited by LDHall; 04-21-2020 at 07:14 AM.
    Reply With QuoteRegister To Reply
  4. 04-21-2020, 07:28 AM #4
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Array formula with multiple match criteria to count exact phrase within a string of te

    Hi,

    Safer to switch to SUMPRODUCT in such cases.

    In G3:

    =SUMPRODUCT(0+($B$2:$B$21=$F3),0+($C$2:$C$21=$F$2),0+ISNUMBER(SEARCH(", "&G$2&", ",", "&$D$2:$D$21&", ")))

    copied across and down to AH5.

    Also copiable to the other two ranges, though appropriate adjustments to the parts $F$2 and G$2 will need to be made.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  5. 04-21-2020, 07:32 AM #5
    LDHall is offline
    Registered User
    Join Date
    04-03-2020
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    12

    Re: Array formula with multiple match criteria to count exact phrase within a string of te

    Perfect! Thank you so so much!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Index Match with Multiple Criteria (exact for text and greater than value for number)
    By mxlew in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-26-2021, 05:36 AM
  2. Countif exact phrase within a string of text
    By LDHall in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-03-2020, 11:55 AM
  3. [SOLVED] index match - 2 criteria (Date & String) one needs to be less than the other exact
    By timhuls1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-04-2017, 08:34 AM
  4. Formula needed for partial string match as part of index match with multiple criteria
    By Marvelous in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  5. Multiple Criteria search and COUNT FOR TEXT across multiple sheet ARRAY's
    By akaushik25 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2014, 08:58 AM
  6. [SOLVED] Find Exact Match Text String Using Formula
    By sweetrevelation in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-12-2012, 10:53 AM
  7. [SOLVED] Count if - not exact match - from cell not string
    By s_twigge in forum Excel General
    Replies: 2
    Last Post: 06-27-2012, 10:44 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1