+ Reply to Thread
Results 1 to 3 of 3

COUNTIFS function makes excel slow, how to make the function "shorter"

Hybrid View

  1. 12-07-2015, 06:55 AM #1
    Gifupack
    Gifupack is offline
    Registered User
    Join Date
    12-07-2015
    Location
    Finland
    MS-Off Ver
    2010
    Posts
    1

    COUNTIFS function makes excel slow, how to make the function "shorter"

    Hi,
    I basically have quite simple task to do. I need to get information from 13 different sheets, which all have year in M -column and month in L -column.
    I need to count how many times certain months of certain year appear in the workbook in these columns. I have years from 2000 to 2015 (soon 2016). I have to have these month-year combinations calculated in quarter years Q1-Q4 (month 01,02,03 - 04,05,06 - 07,08,09 - 10,11,12) per each year. I have used the function bellow, but this causes the excel to slow down, since there are 64 (next year 68) instances with similar heavy-duty-stoneage calculation (Q1/2000, Q2/2000, Q3/2000, Q4/2000, Q1/2001, Q2/2001...etc...) So how I can do the same thing as this COUNTIFS -monster, with some lighter function..? There are also sheets that I do not want to be included in the calculation.

    =COUNTIFS('sheet1'!M:M;"=2000";'sheet1'!L:L;"=01")+COUNTIFS('sheet1'!M:M;"=2000";'sheet1'!L:L;"=02")+COUNTIFS('sheet1'!M:M;"=2000";'sheet1'!L:L;"=03")+COUNTIFS('sheet2'!M:M;"=2000";'sheet2'!L:L;"=01")+COUNTIFS('sheet2'!M:M;"=2000";'sheet2'!L:L;"=02")+COUNTIFS('sheet2'!M:M;"=2000";'sheet2'!L:L;"=03")+...etc... this continues for years from 2000 to 2015 and includes every month, so the function is rather long.

    I also need to have this table updated in "real time". So if I add more rows that have these month/year combinations, they are calculated immediately to the table.

    I'm not so PRO with excel and have done only rather basic stuff, so help would be mutch appreciated.
    Reply With QuoteRegister To Reply
  2. 12-07-2015, 07:01 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: COUNTIFS function makes excel slow, how to make the function "shorter"

    It would be easier to follow all this on an excel sheet. Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    The paperclip icon
    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
    Reply With QuoteRegister To Reply
  3. 12-07-2015, 07:01 AM #3
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: COUNTIFS function makes excel slow, how to make the function "shorter"

    Hi and welcome to the forum

    The first thing You should create dynamic range names for your data ranges in column M & L so that the range names only cover the actual range of data. If that doesn't help or for more clarification upload the workbook.

    Re the last point. You may need a macro for this.

    The other thing I'd seriously consider is the need to have your data spread across many monthly sheets. I really feel you are giving yourself a headache that's unnecessary. Whenever you have a need to summarise and analyse data of this sort create a single sheet database which holds all your data. Then analysis can be performed in seconds by using a Pivot Table. It may be sufficient to use the layout on your existing monthly sheets and just add another column which records the date, but we wont know until we see the workbook.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Reference VBA object within worksheet function "countifs"
    By pwells in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-26-2014, 08:31 AM
  2. [SOLVED] Countifs problem - Using the "OR" function or someting similar
    By shepherdc2814 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2014, 05:58 PM
  3. Track Changes ( Makes "Undo/Redo" Function Unavailable - Any VBA to Re-Add it?)
    By karimel_romeo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-29-2013, 12:22 PM
  4. VBA: Using "<=" symbol with Countifs Function
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-08-2013, 08:33 PM
  5. overuse of the sentence ".Select" to perform various functions makes my program is slow
    By yosma in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-05-2013, 03:31 PM
  6. cell references in "COUNTIFS" function
    By mmrtech in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-30-2007, 04:25 PM
  7. "Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O)
    By Alain79 in forum Excel General
    Replies: 4
    Last Post: 06-14-2005, 03:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1