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:

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.