+ Reply to Thread
Results 1 to 6 of 6

COUNTIFS with Multiple Text Criteria in Same Criteria Range

  1. #1
    Registered User
    Join Date
    06-17-2017
    Location
    LONDON
    MS-Off Ver
    MS OFFICE 2K16
    Posts
    3

    COUNTIFS with Multiple Text Criteria in Same Criteria Range

    As per title,

    looking for a shortcut or alternative to type;

    =SUM(COUNTIF(H2:H200{"A","B","C","D","E"}))

    where A,B,C,D,E.....Z are several long text statements that I have listed, it would take a great deal of time to copy and paste then add quote marks and commas...

    Thanks in advance

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: COUNTIFS with Multiple Text Criteria in Same Criteria Range

    Hi and welcome to the forum

    One way or another you'll need to create a list of the values to test.

    Why not just list the text strings you want to count in say column A and use a COUNTIF(H$2:H$200,A2) in say column B copied down. Then sum column B
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-17-2017
    Location
    LONDON
    MS-Off Ver
    MS OFFICE 2K16
    Posts
    3

    Re: COUNTIFS with Multiple Text Criteria in Same Criteria Range

    Thanks for the welcome and the swift reply Richard,

    without being too specific, the end result of the project would be users picking any single value from text values A-Z and recording it in a single column. My aim is to produce a counter that counts the result of a group of those values, for example counting together all of those who chose A-G and K-N. The amount of people who chose D in the case of this project is not important enough to be counted seperately and summed on the main sheet. Your method is indeed valid and I am grateful,If it comes to it I will use it just on another sheet, however I am looking for something more streamlined, if it means I have to copy and paste the text one by one I may have to.

    Someone suggested to me that the list of values can be converted to CSV in such a way that they gain the necessary commas and quote marks. Is this true?

    Thanks in advance

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: COUNTIFS with Multiple Text Criteria in Same Criteria Range

    Saving a file as a .csv will add the commas between fields when you view the .csv file in a text editor. If you want to include quote marks as well then you'd need to pre-process your data with a macro and add them.

    However if you just want the string to include in your array formula then assuming the text strings are in B1, C1, D1.....etc then in B2 add

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and copy across.
    Now go the the last cell and copy the formula and paste back as values. All you need then do is remove the final trailing comma.

    BTW, in your original formula the SUM bit is redundant.

  5. #5
    Registered User
    Join Date
    06-17-2017
    Location
    LONDON
    MS-Off Ver
    MS OFFICE 2K16
    Posts
    3

    Re: COUNTIFS with Multiple Text Criteria in Same Criteria Range

    Thanks so much Richard, your last post is exactly what I'm looking for. Much appreciated.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: COUNTIFS with Multiple Text Criteria in Same Criteria Range

    My Pleasure...

+ 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 with multiple criteria in single criteria range
    By SUHAS KARHADKAR in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 12-01-2016, 10:55 AM
  2. Countifs with multiple criteria in single criteria range
    By MCP313 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2016, 03:51 AM
  3. Countifs with multiple criteria and data range
    By Pritirus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-21-2016, 01:18 PM
  4. [SOLVED] COUNTIFS with multiple criteria in the same criteria range
    By Faridwahidi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-23-2015, 01:04 PM
  5. [SOLVED] COUNTIFS multiple criteria (date range & text)
    By jholiday78 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-30-2014, 06:36 PM
  6. COUNTIFS with Multiple Criteria in One Range
    By mphillips in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-19-2013, 02:43 PM
  7. COUNTIFS for multiple text criteria
    By lallo.jr in forum Excel General
    Replies: 5
    Last Post: 05-21-2010, 12:25 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