+ Reply to Thread
Results 1 to 5 of 5

Help with filters and counters

  1. #1
    Registered User
    Join Date
    11-30-2019
    Location
    Costa Rica
    MS-Off Ver
    2016
    Posts
    2

    Help with filters and counters

    Hi team,

    I know this is going to sound weird, but I'm going to use this example since the real data is private.

    So I have this drop-down list of fruits, it's already set up in Column A, and right next to it, there's Column B which asks if it has been eaten yet. Now, what I'm trying to do is that in Column E only the eaten fruits show up, like filtered, and next to it, how many have been eaten.

    If you guys could help me with it I'd really appreciate it, I know some advanced stuff, but this is like pro level to me.

    Thanks in advanced!
    validation data.PNG
    sheet2.PNG
    Attached Files Attached Files
    Last edited by mortifiedfry; 12-01-2019 at 02:30 PM. Reason: added attachment

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,243

    Re: Help with filters and counters

    You can add an attachment - follow the instructions given in the yellow banner at the top of the screen. Do not try to use the Paperclip icon for attachments, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-30-2019
    Location
    Costa Rica
    MS-Off Ver
    2016
    Posts
    2

    Re: Help with filters and counters

    Thanks a lot, I was able to add the attachment now

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,243

    Re: Help with filters and counters

    You can put this formula in cell C2:

    =IF(AND(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,B2="yes"),MAX(C$1:C1)+1,"-")

    It should copy down automatically, and give you a series of sequential numbers which help to identify the fruits that have been eaten.

    Then you can use this formula in E2:

    =IFERROR(INDEX(A:A,MATCH(ROWS($1:1),C:C,0)),"")

    and this one in F2:

    =IF(E2="","",COUNTIFS(A:A,E2,B:B,"yes"))

    Hope this helps.

    Pete

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 VersionVersion 2501 Win 11
    Posts
    24,434

    Re: Help with filters and counters

    Here is a VBA alternative
    Please Login or Register  to view this content.
    How to install your new code
    • Copy the Excel VBA code
    • Select the workbook in which you want to store the Excel VBA code
    • Press Alt+F11 to open the Visual Basic Editor
    • Choose Insert > Module
    • Edit > Paste the macro into the module that appeared
    • Close the VBEditor
    • Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


    To run the Excel VBA code:
    • Press Alt-F8 to open the macro list
    • Select a macro in the list
    • Click the Run button
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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. [SOLVED] Trying to create a dynamic table so I can create a updating drop down list.
    By sbobster15 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-16-2019, 11:43 AM
  2. Create Filter automatic filter of list in other sheet
    By martijnrikkert in forum Excel General
    Replies: 4
    Last Post: 12-30-2018, 07:33 PM
  3. Drop Down List with Filter and the Filter list in one single column
    By wpm7113 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-28-2016, 06:38 AM
  4. How to replace drop down list filter to those normal excel filter?
    By cks1026 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-24-2016, 02:48 AM
  5. Replies: 5
    Last Post: 03-02-2015, 02:32 PM
  6. How to create a drop down list based on the choices of another drop down list?
    By Happy jain in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-27-2013, 01:54 AM
  7. Create List using values in Filter drop down
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-10-2007, 02:14 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