COUNTIFS and excluding some text + excluding some data in a calculation

I have attached a sample file.

I have a file with roughly 200 worksheets. Each worksheet represents a group of people and a time period.

I need to do calculations on each worksheet. I am hoping to create a column of calculations that I can just paste into each worksheet (i.e. when I group them) and save me several days of work.

The flow in general is:
Download the raw data worksheet --> transform raw data into something usable (i.e HH:MM data into decimal data) --> do the caluclations

My challenges are:

1. Can't get the COUNTIF data exclusion to work (i.e. <>"...")
2. My raw data is not always on the same row in each worksheet. I have been manually moving the calculations around to line with the same row but that is time consuming with so many sheets.
3. In general the raw data is in a format and data type that is hard to work with, e.g. TEXT with HH:MM data

I have tried to segment the sample file to make it understandable:
4 sections:

1. Raw data summary
2. Raw data individual data (both summary and individual data are downloaded)
3. Transform individual raw data into something usable (i.e. HH:MM to decimal)
4. Create a self-defined group of individual data to do calculations on (I tried INDEX-MATCH but it didn't work)

Apologies, but this is a lot of questions and a fairly complicated spreadsheet and I am not 100% clear how it works and what it is supposed to achieve exactly.
I describe below my understanding and solutions to certain individual problems.
And I attach a revised worksheet, all changes marked in yellow, based on what I think you're trying to achieve.
Certain formulas also look wrong to me, e.g. "Average>0" - you're ignoring zeroes for the average but where an actual items yields zero, that very zero should not be ignored. Otherwise your average is wrong.
But this is just me guessing what the outcome should be like.

For problem 3 as denoted in the Excel file, the following is a possible solution:

Please Login or Register  to view this content.

btw - I would avoid full column references, it's slow and error prone (e.g. if non-numeric headings are included etc.)

If you prefer to stick with COUNTIFS, try the following:

Please Login or Register  to view this content.

Next topic: for "COUNT ONLY THESE IDENTIFIERS" I would suggest the following:
Create a named range of unique identifiers first:
1) Helper column to identify all unique Identifiers:

Please Login or Register  to view this content.

- for me this formula is in AU23
The result is the same as the above formulas (164), so it appears there are no duplicates identifiers.
2) List all unique identifiers:

Please Login or Register  to view this content.

- for me the formula starts in AS25
Fill this formula as far down as necessary - at least 164 rows in this case; it must be array entered with CTRL+SHIFT+ENTER
3) Create a Names Range for these values and make the range dynamic, e.g. in this form:

Please Login or Register  to view this content.

4) Use this dynamic range for data validation for the "COUNT ONLY THESE IDENTIFIERS" - this list could further be sorted alphabetically (either manually or via formula, but the formulas are not
straight forward, so I'll save this bit for now)

And so on.
Have a look if the attached sheet already solves some of your problems.

As general advice because you said the data download does not always start in the same row etc.:
- Sheet 1 = Raw Data Copy Pase: Keep one input sheet where you just raw input the data on a copy paste basis
- Sheet 2 = Cleaned Up Rawe Data: Do another sheet where you bring the raw data into the same format, e.g. by determining where the data starts and then offsetting all data from the original data into the cleaned up input data sheet
- Sheet 3 = Output Sheet: Have a third sheet as the output sheet which uses only the cleansed up input data sheet: this should make formulas more straight-forward; e.g. your raw data seem to be stored as text etc. -
I e.g. quick and dirty fixed this with a the double unary character "--" infront of some formulas

Hope this help and is going into the right direction.

Any feedback, let me know.

Regards

Thanks RaulSerg!

I got most of the formulas/suggestions integrated.

In your attached file it was not clear what you are using the data in column AS for.

Only trouble I am having is with the COUNTIFS.

Right now the formula is:

Formula:

Please Login or Register  to view this content.

I think it is counting formulas that result in a blank. The formula that COUNTIFS is looking at is:

Formula:

Please Login or Register  to view this content.

So...can I adjust the COUNTIFS formula to exclude formulas in the counting?

So, my file is about 105 MB with 322 worksheets and obviously I am not able to do any work on it since Excel crashes.

It is basically 7 worksheets for 46 periods. This will grow was more periods get added.

Do you think 1 file with 7 worksheets per period would be better?
Then I will have a separate file that links to 46 files (and growing) to collate the data?