Use CELL function for SheetName in formula to retrieve data

Your post shows COUNTIF. The upload shows SUMIF. I went with SUMIF in C6.

Formula:

Please Login or Register  to view this content.

Does this do what you want?

Thanks Dave! Yes that works for the first part of the problem. I accidentally used SUMIF/COUNTIF, but it doesn't matter, just working to learn how to write a formula. What you have done is the perfect start, but two problems remain.

The first of the two remaining problems is that only the first CVL was dealt with.
The second CVL near the end still needs to use the CELL function, otherwise, when I use this formula with the sheetName XYZ it won't work because it'd be looking for the NamedRange CVL_RASS_ instead of NamedRange XYZ_RASS_. I see it'll be pretty hairy because the whole thing for the second CVL will fall outside of the closing single quote but inside of the closing double quote. WOW!

The second and major remaining problem is that I'm not actually trying to get you to write this for me, it's only a test.
Since I'm obviously not smart enough to figure out how to do this, I'm really seeking to LEARN HOW to do this and to UNDERSTAND IT so I can do this kind of thing in the future. I'm hoping you can share an explanation with me simple enough for my feeble mind to GET IT. I'm just baffled about how you 'knew' to put the !CVL_RASS_ inside of the double quotes. I think I understand that the beginning and ending single quotes encompass everything on the sheetName side of the !.

What I'm seeing, (right or wrong), is that you put double quotes around the opening single quote (to make it literal TEXT?) AND you put double quotes around the closing single quote (to also make that quote literal TEXT?) BUT have included the remote sheetName range within the second set of double quotes. I'm guessing that is because the entire Indirect function needs to be inside of the quotes???
...or is the double quote in front of each single quote just an escape character???

Continued help from You or Anyone out there is Greatly appreciated.

The single quotes are required for the sheet name(s) as well as the !. Wrapping the CVL_RASS_ inside the quotes was an educated guess derived from the fact that is how literal cell ranges are treated. Indirect syntax often confuses me. I don't use it very often in cross sheet references so I resort to trial and error. I managed to get this one right the first time.

Having said that I will do what I can to work with you on the rest of this. It will take me time and I will need another upload that reflects what you mean by XYZ and XYZ_RASS_ ... and others. I need to see the layout(s) in context of the workbook. I am not the best "go to guy" for this function, and building these strings dynamically may prove to be a challenge.

Yes INDIRECT takes literal text. In order to address other sheets and ranges you have to build your reference strings. You've got the basic idea of how to use MID/CELL/FIND to extract the desired strings. So that much is solved. We'll muddle through the rest if necessary, but truth be known we may already have the solutions.

Let me know.

Perhaps, hope this works, and if not just @Dave said, try to explain in a simple way about your desired results

=SUMPRODUCT(SUMIF(INDIRECT("'"&$C$4&"'!B1:B100"),"<>",INDIRECT("'"&$C$4&"'!B1:B100")))

Good point azumi. Never occurred to me to just extend the ranges.