Countifs on only visible data after filtering

    Hi, what do I need to add to the below code to only count the visible data after filtering. I assume it is possibly .SpecialCells(xlCellTypeVisible) or similar after With Worksheet("calculations"), but I can't figure it out. Help appreciated.




    Excel already has a COUNTA Function, so you shouldn't be using that as a name for a UDF.


    Why do you need VBA for this? Inbuilt formulas will be much more efficient than writing code.


    Attach an example workbook and explain what you want to achieve.


    The SUBTOTAL Function will only work on Filtered Rows.


    You should be using the Table feature of Excel then you can add a Total Row which be dynamic and can be set to use COUNT as a SUBTOTAL formula.


    Overview of Excel tables

    Hi Roy, I didn't realise that about CountA, I will change it. I have others such as CountB etc in the same large macro. The code was given to me and I'm just trying to make changes to suit my needs.


    I'm currently filtering the data for this month, C&P to a separate sheet, running the code and repeating the procedure then for last month, last quarter etc.


    I was hoping I could just filter the data, run the code, re-filter and run the code etc without all the C&P'ing. So I need the code to only use the visible data.


    I have attached a mockup.

    Ozgrid Visible only test.xlsm

    Hello,


    Based on your test file, if you actually need to filter and copy visible rows to sheet x ... the countifs becomes a bit irrelevant ...;)


    Can you confirm you do need to filter and copy post-filter results to another sheet ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    I've formatted your data as a Table and added the Count Function to Notice Creation Date - Date. Now you can filter by Months, Years or specified dates. After filtering the COUNTIF will be displayed at the bottom of the Column.


    This is the best way to manage Data in Excel. Take a look at post back with your comments.

    Quote from Carim

    Hello,


    Based on your test file, if you actually need to filter and copy visible rows to sheet x ... the countifs becomes a bit irrelevant ...;)


    Can you confirm you do need to filter and copy post-filter results to another sheet ...

    Hi Carim, I'm trying to eliminate copying and pasting.......I just want to filter and count only the visible data with the countifs formula which populates on sheet x.

    Quote from royUK

    I've formatted your data as a Table and added the Count Function to Notice Creation Date - Date. Now you can filter by Months, Years or specified dates. After filtering the COUNTIF will be displayed at the bottom of the Column.


    This is the best way to manage Data in Excel. Take a look at post back with your comments.

    Hi Roy, I need to populate sheet x with the countifs after I filter.....I'm trying to eliminate C&P to another sheet after filtering. I want to count only the visible data after I filter.

    I asked why you needed to copy to a different sheet.


    I can also see no need for your VBA code, it's easier to use the functions in the sheet. COUNTIFS can work with a date range.


    Which column are you filtering? Explain what you are trying to do and I am sure there is a better way.

    Try the attached file


    Click the appropriate button for the date range required for counts.


    You have not specified which date column is to be used for the filtering, the codes assigned to the buttons use the dates in Column K. Change to suit where indicated in each procedure.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited once, last by KjBox ().

    Hi Roy and KJ, if I filter my data and run my countifs code it counts the hidden data also, so I have to filter and then copy and paste the data to another sheet before I run the countifs code. What I need is to add something like visible cells only to my code so it only counts the visible data which will eliminate the need to copy and paste the filtered data to another sheet before I run the code. I don't just filter the date, I filter for numerous thing numerous times. It has to be VBA because it is just a small part of a very large macro used by several users. Thanks for your help so far, cheers.

    Have you actually tried the file I attached?


    It calculates all the counts for all the conditions that you use with your COUNTIFS code plus 4 different date conditions (current month, previous month, current quarter and previous quarter)


    There is no need for any actual filtering and/or copy pasting.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Yes it's incredible....completely eliminates the need for filtering, copying and visible cells that I was asking for and the hundreds of lines of my code.


    It is way above my knowledge level but I will try to figure it out so I can adapt it to all the other things I also need to filter for.


    Thank you very much.

    Try replacing your existing Sub and Function with this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited 2 times, last by KjBox ().