+ Reply to Thread
Results 1 to 5 of 5

How to count data in cells with more than one criteria in same cell

  1. #1
    Registered User
    Join Date
    02-01-2018
    Location
    Pickering, North Yorkshire
    MS-Off Ver
    Excel 2016
    Posts
    40

    How to count data in cells with more than one criteria in same cell

    Hi, I wonder if you can help me.
    I download a report from our system and it is a csv file, so I have to manipulate it to get the data I need to import into another system.
    However the way the report generates, it combines multiple values in the same cell.
    I have attached a small snippet of the spreadsheet and wondered if you could help. Column K needs to count all the entries that have a numeric value only. I have for this example, manually typed what the result should be.
    I have tried different count variations, but it only seems to give part of the result I am looking for. I'm obviously doing something wrong.
    I'm a novice, so there may be another solution but I'm not sure what it is.
    Any help would very much be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to count data in cells with more than one criteria in same cell

    Hi,

    In K2:

    =COUNT(1/MMULT({1,1,1,1,1,1,1,1,1,1},N(ISNUMBER(FIND({0;1;2;3;4;5;6;7;8;9},D2:J2)))))

    and copied down.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    02-01-2018
    Location
    Pickering, North Yorkshire
    MS-Off Ver
    Excel 2016
    Posts
    40

    Thumbs up Re: How to count data in cells with more than one criteria in same cell

    Hi XOR LX,
    That is AMAAAAAZING!!
    I have copied that into K2 and pulled down and it works.
    I then adjusted the formula to go into the main spreadsheet and it works PERFECTLY!
    Thank you so, so much.
    I have never heard of this type of formula before, so I am truly grateful.
    Thank you.

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to count data in cells with more than one criteria in same cell

    You're welcome! Glad to hear it!

    Cheers

  5. #5
    Registered User
    Join Date
    11-27-2020
    Location
    Ahmedabad
    MS-Off Ver
    2013 and 2016
    Posts
    46

    Re: How to count data in cells with more than one criteria in same cell

    Excellent XOR LX

    Solving this since an hour and ended up with this, didn't know about 1/MMULT

    =IF(LEN(TRIM(SUBSTITUTE(IF(OR(ISNUMBER(D2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},D2))),MID(D2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},D2),1),IF(ISERROR(FIND("/",D2)-1),99,FIND("/",D2)-1))+0,0)&" "&IF(OR(ISNUMBER(E2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},E2))),MID(E2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},E2),1),IF(ISERROR(FIND("/",E2)-1),99,FIND("/",E2)-1))+0,0)&" "&IF(OR(ISNUMBER(F2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},F2))),MID(F2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},F2),1),IF(ISERROR(FIND("/",F2)-1),99,FIND("/",F2)-1))+0,0)&" "&IF(OR(ISNUMBER(G2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},G2))),MID(G2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},G2),1),IF(ISERROR(FIND("/",G2)-1),99,FIND("/",G2)-1))+0,0)&" "&IF(OR(ISNUMBER(H2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},H2))),MID(H2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},H2),1),IF(ISERROR(FIND("/",H2)-1),99,FIND("/",H2)-1))+0,0)&" "&IF(OR(ISNUMBER(I2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},I2))),MID(I2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},I2),1),IF(ISERROR(FIND("/",I2)-1),99,FIND("/",I2)-1))+0,0)&" "&IF(OR(ISNUMBER(J2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},J2))),MID(J2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},J2),1),IF(ISERROR(FIND("/",J2)-1),99,FIND("/",J2)-1))+0,0),0," ")))=0,0,LEN(TRIM(SUBSTITUTE(IF(OR(ISNUMBER(D2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},D2))),MID(D2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},D2),1),IF(ISERROR(FIND("/",D2)-1),99,FIND("/",D2)-1))+0,0)&" "&IF(OR(ISNUMBER(E2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},E2))),MID(E2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},E2),1),IF(ISERROR(FIND("/",E2)-1),99,FIND("/",E2)-1))+0,0)&" "&IF(OR(ISNUMBER(F2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},F2))),MID(F2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},F2),1),IF(ISERROR(FIND("/",F2)-1),99,FIND("/",F2)-1))+0,0)&" "&IF(OR(ISNUMBER(G2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},G2))),MID(G2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},G2),1),IF(ISERROR(FIND("/",G2)-1),99,FIND("/",G2)-1))+0,0)&" "&IF(OR(ISNUMBER(H2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},H2))),MID(H2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},H2),1),IF(ISERROR(FIND("/",H2)-1),99,FIND("/",H2)-1))+0,0)&" "&IF(OR(ISNUMBER(I2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},I2))),MID(I2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},I2),1),IF(ISERROR(FIND("/",I2)-1),99,FIND("/",I2)-1))+0,0)&" "&IF(OR(ISNUMBER(J2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},J2))),MID(J2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},J2),1),IF(ISERROR(FIND("/",J2)-1),99,FIND("/",J2)-1))+0,0),0," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(IF(OR(ISNUMBER(D2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},D2))),MID(D2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},D2),1),IF(ISERROR(FIND("/",D2)-1),99,FIND("/",D2)-1))+0,0)&" "&IF(OR(ISNUMBER(E2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},E2))),MID(E2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},E2),1),IF(ISERROR(FIND("/",E2)-1),99,FIND("/",E2)-1))+0,0)&" "&IF(OR(ISNUMBER(F2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},F2))),MID(F2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},F2),1),IF(ISERROR(FIND("/",F2)-1),99,FIND("/",F2)-1))+0,0)&" "&IF(OR(ISNUMBER(G2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},G2))),MID(G2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},G2),1),IF(ISERROR(FIND("/",G2)-1),99,FIND("/",G2)-1))+0,0)&" "&IF(OR(ISNUMBER(H2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},H2))),MID(H2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},H2),1),IF(ISERROR(FIND("/",H2)-1),99,FIND("/",H2)-1))+0,0)&" "&IF(OR(ISNUMBER(I2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},I2))),MID(I2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},I2),1),IF(ISERROR(FIND("/",I2)-1),99,FIND("/",I2)-1))+0,0)&" "&IF(OR(ISNUMBER(J2),ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},J2))),MID(J2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},J2),1),IF(ISERROR(FIND("/",J2)-1),99,FIND("/",J2)-1))+0,0),0," "))," ",""))+1)

+ 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: 07-15-2020, 10:52 AM
  2. [SOLVED] Count data based on color of cell with criteria
    By HaroonSid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-16-2017, 04:26 AM
  3. Replies: 12
    Last Post: 03-23-2017, 01:24 PM
  4. [SOLVED] Count cells in range that match corresponding cell, and meet an additional criteria.
    By Nyima in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-15-2016, 04:11 PM
  5. Count cells based on criteria of another cell
    By codyj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-07-2010, 02:24 PM
  6. Sum Cells if Another Cell Meets Criteria and Stop When Count is Met
    By obatiz in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 01-15-2009, 09:13 AM
  7. [SOLVED] How do I count nonblank cells that meet criteria in another cell?
    By jimswinder in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 07-20-2006, 11:45 AM

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