+ Reply to Thread
Results 1 to 8 of 8

Using COUNTA to count across multiple sheets

  1. #1
    Registered User
    Join Date
    04-06-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    20

    Using COUNTA to count across multiple sheets

    Hi ,

    I am having almost 200 to 300 sheets. In each sheet from column F2: till the end, I wanted if any value exists in this column consolidate all present me number.

    I have tried the below code, it iterates and prints only one number that is present in the first sheet, but it does not picks up from other sheets and sum it print. Please kindly help with the correction in this Macro.

    Please Login or Register  to view this content.
    Thank You

  2. #2
    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: Using COUNTA to count across multiple sheets

    Hi,

    Try something like

    Please Login or Register  to view this content.
    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.

  3. #3
    Registered User
    Join Date
    04-06-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Using COUNTA to count across multiple sheets

    Hi richard It is pulling only one sheet of data. rest others are not considered.

  4. #4
    Registered User
    Join Date
    04-06-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Using COUNTA to count across multiple sheets

    One more macro found works, but it counts even blank entries. can we eliminate those blanks and consider only actual entries.

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Using COUNTA to count across multiple sheets

    Do you mean?
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-06-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Using COUNTA to count across multiple sheets

    Thank you jindon, this helps me, as my excel sheet also used to contain merged cells and blank cells in between those columns, your code above help me to consider only available values and while not considering merge cell values and blanks.

    Thank you once again jindon

  7. #7
    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: Using COUNTA to count across multiple sheets

    Quote Originally Posted by jake29 View Post
    Hi richard It is pulling only one sheet of data. rest others are not considered.
    I did ponder what exactly you expected but it seemed to me after a bit of head scratching that since you mentioned a 'number' rather than numbers, and then went on to imply from your code that you were looking for the number of non blank cells on each sheet to appear in B1 on EACH sheet.

    Nowhere did you mention a totalling of 200-300 sheets.

    If you want a grand total for all sheets then set a variable to increase at every pass through the loop and then pass the variable value back to whatever cell you want in the workbook.

    Otherwise please clarify. Worked examples are worth a thousand words. You needn't upload 300 sheet, half a dozen as an example would be fine .

  8. #8
    Registered User
    Join Date
    04-06-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Using COUNTA to count across multiple sheets

    Thank you, Richard, I think maybe you have overlooked it. I have mentioned it was the first sentence pointing I have 200 to 300 odd sheets and if I used a number of sheets then I was expecting 'numbers'.

    that was the last resort I had prepared to upload a sample file. so, that would be easy to track down. But the other user had already delivered me the working code.

    Thank you.

+ 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. CountA / CountIF across multiple sheets
    By Motoxboi26 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-27-2020, 02:47 AM
  2. [SOLVED] Count, counta & countif
    By Noobulon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-18-2017, 04:55 PM
  3. Replies: 5
    Last Post: 02-06-2015, 08:44 PM
  4. [SOLVED] Count blanks or counta
    By bopsgtir in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-15-2012, 01:43 PM
  5. Need help on doing COUNT or COUNTA on Pivot table
    By enricotambunan in forum Excel General
    Replies: 2
    Last Post: 06-04-2012, 11:41 PM
  6. Using CountA to count cells of a certain color
    By Richard2012 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-12-2012, 03:16 PM
  7. [SOLVED] Count or CountA
    By Arturo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2005, 02:06 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