+ Reply to Thread
Results 1 to 6 of 6

Counting Multiple fields in 1 column

  1. #1
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Counting Multiple fields in 1 column

    I don't know if this is possible, but you lot are wizards so I'll give it a go

    I have some software that outputs multiple fields into one column. These fields are separated by a ** (attached)

    Is it possible to dynamically count how many times (in this case how many 1000's) there is a value between fields separated by the **?

    The yellow shows the correct numbers but this is done manually

    Sean
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Counting Multiple fields in 1 column

    With data starting in A2...

    Helper in B2:
    =IF(LEFT(A2,2)="**",A2,B1)

    Array formula in C5:
    =IFERROR(INDEX(A:A,SMALL(IF(LEFT($A$2:$A$24,2)="**",ROW($A$2:$A$24)),ROWS($1:1))),"")

    Formula in D5:
    =IF(C5="","",COUNTIF(B:B,C5)-3)

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Counting Multiple fields in 1 column

    Inspired by Glenn Kennedy
    C4=IFERROR(INDEX(A:A,SMALL(IF(LEFT($A$1:$A$24,2)="**",ROW($A$1:$A$24)),ROWS($1:1))),"")
    (As suggested by Glen Kennedy)
    D4=SUM(INDEX((LOOKUP(ROW($A$1:$A$23),ROW($A$1:$A$23)/(LEFT($A$1:$A$23,2)="**"),$A$1:$A$23)=$C4)*($A$1:$A$23=1000),0))
    Try the above formulas and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: Counting Multiple fields in 1 column

    Absolutely genius!

    I'd gone down the path of making a count of how many "**" there are then assigning each category I wanted counting an ID number. And then counting how many times that ID happened

    Your method is far superior and cleaner, thank you!

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Counting Multiple fields in 1 column

    A non-array alternative for C5:
    =IFERROR(INDEX(A:A,SMALL(INDEX((LEFT($A$2:$A$24,2)<>"**")*10^10+ROW($A$2:$A$24),0),ROWS($1:1))),"")

    and an alternative for D5:
    =IFERROR(1/(1/SUMPRODUCT(--($B$2:$B$24=C5)*(ISNUMBER($A$2:$A$24)))),"")

  6. #6
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: Counting Multiple fields in 1 column

    Glenn Kennedy

    Thanks again, I ended up going for your non-array version to keep things moving fast. Works perfectly!

+ 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. Calculate different where there are multiple values in column fields
    By stephme55 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-07-2016, 03:13 PM
  2. [SOLVED] Targeting Fields and Counting Up or Down Into Other Pre-determined Fields
    By techjob in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2013, 09:28 AM
  3. Multiple pivot tables: swith column/value fields for all at once
    By brightkanon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2013, 06:04 AM
  4. Counting the number of fields in a column for which there are duplicate values.
    By sangreal2007 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-24-2013, 03:59 AM
  5. [SOLVED] Counting Multiple Values in a column based on critera in seperate column
    By ERoberts in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2013, 01:08 PM
  6. [SOLVED] Populate Multiple Fields from Mutiple Column Listbox
    By sgwilliams in forum Excel General
    Replies: 6
    Last Post: 02-07-2011, 02:47 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