+ Reply to Thread
Results 1 to 2 of 2

dynamic formula to count through existing and new sheets(In google sheets)

  1. #1
    Registered User
    Join Date
    06-06-2014
    Location
    Nepal
    MS-Off Ver
    MS office professional 2016
    Posts
    41

    dynamic formula to count through existing and new sheets(In google sheets)

    I need to help on count formula ...if i add new sheet formula should adjust it accordingly .i don't want to edit formula every time i add new sheets along with data.
    Attached Files Attached Files

  2. #2
    Forum Expert hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,249

    Re: dynamic formula to count through existing and new sheets(In google sheets)

    FIRST, there are spaces following Baishak and jestha in worksheet names, but no space after Aahad. Careless inconsistency doesn't help anyone.

    If this needs to be in Google Sheets, please IGNORE the yellow box near the top of all pages on this site. It's FAR LESS USEFUL for you to attach an Excel workbook than providing a link to a Google Sheets example when the rest of us would just have to download an .XLSX file, upload it to Google Drive, then convert the .XLSX file to Google Sheets.

    Neither Excel nor Google Sheets can use COUNTIF across multiple worksheets using 3D references. Both require trickery using the volatile INDIRECT function. If you had all worksheet names in a range named WSList, use

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    However, YOU would need to maintain WSList.

    This sort of variable number of worksheets processing is something Excel and most other existing spreadsheets do VERY POORLY. For what little it's worth, only Quattro Pro does this moderately well because its 3D range syntax AND semantics are based on Lotus 1-2-3 rather than Excel. You would be FAR BETTER OFF consolidating data from multiple worksheets into a single worksheet and using that consolidated data for conditional aggregation calculations. You are begging and pleading for A LOT MORE PROBLEMS trying to work directly with data from a variable number of worksheets in Google Sheets especially since it doesn't even support 3D references like Sheet1:SheetN!A1. There are many good features in Google Sheets, but it by far the WEAKEST in 3D/multiple worksheet functionality. Meaning that if you MUST use Google Sheets, a great number of things will be MUCH MORE DIFFICULT than they'd be in Excel or LibreOffice Calc. This is one of those things.
    Last edited by hrlngrv; 11-26-2020 at 09:19 PM. Reason: typos

+ 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. Google Sheets: need to dynamic formula for unique value and count multiple same value
    By wlinksanju in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 11-21-2020, 10:29 PM
  2. Google Sheets: Merge dynamic ranges automatically (skipping rows without a visible data)?
    By Romkeris in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 15
    Last Post: 06-21-2020, 09:27 AM
  3. GOOGLE SHEETS: countif with dynamic range
    By Headburst in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 11
    Last Post: 12-30-2019, 09:59 AM
  4. GOOGLE SHEETS: countif with dynamic range
    By Headburst in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-30-2019, 08:41 AM
  5. Create dependent dynamic range in google sheets
    By frnzy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-17-2019, 06:23 AM
  6. Create dependent dynamic range in google sheets
    By frnzy in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 12-17-2019, 06:23 AM
  7. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM

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