+ Reply to Thread
Results 1 to 3 of 3

Count certain "x" or numbers in array with SUM(FREQUENCY(IF

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    Count certain "x" or numbers in array with SUM(FREQUENCY(IF

    Hi all,

    I'm trying to add more criteria to my SUM(IF(FREQUENCY array formula, but struggling to do so. Currently the formula only considers cells with "x"
    Please Login or Register  to view this content.
    but I want to make it more specific and add below criteria to it. Basically, it should not only count "x", but also " x", "xx", "xxx" or positive numbers such as "2", "5" while excluding zero. It should not count words such as "fox" or "lux" or "-5", "-8" etc.

    new criteria:

    CODE]=OR(REPT("x",{1,2,3,4,5})=SUBSTITUTE(EG13," ",""),N(EG13)>0)[/CODE]

    current FREQUENCY formula that only considers "x"

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count certain "x" or numbers in array with SUM(FREQUENCY(IF

    Try this version

    =SUM(IF(FREQUENCY(IF((ISNUMBER(MATCH(SUBSTITUTE($J$13:$J$29," ",""),REPT("x",{1,2,3,4,5}),0))+(ISNUMBER($J$13:$J$29+0)*($J$13:$J$29>0)))*($I$13:$I$29<>""),--$I$13:$I$29),--$I$13:$I$29),1))

    confirm with CTRL+SHIFT+ENTER
    Audere est facere

  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    Re: Count certain "x" or numbers in array with SUM(FREQUENCY(IF

    That seems to work! Need to do some more testing with my bigger data set tomorrow, but it should be fine... Thanks!!

+ 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: 7
    Last Post: 09-12-2016, 09:43 AM
  2. [SOLVED] Dertiming Maximum count of "1" in a frequency across rows
    By dbeeler in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-13-2014, 06:25 PM
  3. Replies: 3
    Last Post: 02-11-2014, 05:36 PM
  4. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  5. [SOLVED] Count syntax for dividing # of "positive numbers" by "total numbers"
    By synses in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-18-2013, 09:06 PM
  6. Replies: 7
    Last Post: 05-13-2006, 05:02 PM
  7. If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ...
    By Maria J-son in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2006, 08:25 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1