Counting & Summing across select multiple worksheets

[schatham]

I'm trying to figure out some approximate space requirements on a disc based on output from some Powershell scripts whose output data I have imported into Excel. The file structure is similar to this:

z:\MyData\MyFolder\X1
z:\MyData\MyFolder\X2
z:\MyData\MyFolder\Images
z:\MyData\YourFolder\A1
z:\MyData\YourFolder\A2
z:\MyData\YourFolder\A3
z:\MyData\YourFolder\Images

I ran one script to return the file count & folder size of the 2 Images folders. I ran another script to give me the relevant file info for data in the X1, X2, A1, A2 & A3 folders. In that script, I looked for a certain filetype & returned a list of all the files matching that filetype. I can use the count of those rows which will indicate how many images are associated with that particular folder (X1, X2 or A1, A2 & A3).

Using the file count & folder size of the Images folders, I can figure an average image size for that folder.

In Excel, I brought in the results of the 2nd script. I have a worksheet named for each subfolder in z:\MyData (one called MyFolder & one called YourFolder). Also in this same sheet is the data from the images folders & what the average image size is. Another sheet in this same book has a list of all the folder names (X1, X2, A1, A2 & A3).

Here's where I'm stuck.

What I want to do is to be able to search selected worksheets within this workbook (searching the MyData & YourData worksheets), and counting the rows that have folder X1, X2, A1, A2 & A3. I don't want to search all the worksheets in the workbook, just specific ones.

Knowing that the folders:
z:\MyData\MyFolder\Images has 100 files and has a size of 25,000,000 - an average of 250,000 per file and
z:\MyData\YourFolder\Images has 75 files and has a size of 20,000,000 - an average of 266,667 per file

I would like my output in an excel sheet to show:

Folder Avg Img Size Count
X1 250,000 18
X2 250,000 24
A1 266,667 16
A2 266,667 7
A3 266,667 44


from which I can then figure the Img space that particular folder would require. (I already know at this point that X1 is part of MyFolder & that A1 is part of YourFolder.)

I have a couple hundred folder names (X1, X2, A1, A2, A3) which should not repeat, but I can't swear that they won't. What I would like to do is to have the formula that returns the count to be able to look for X1, X2, A1 etc through a specific set of worksheets in this one workbook (actually a specific area of those worksheets) & return that count into a specific cell.

I'd appreciate any ideas about how to go about this. I haven't ruled out doing it in code or as a set of formulas, but would rather change just one or the other should the folder structure change.

Thanks!

 

[schatham]

After thinking about what I was asking, I went back to Powershell & re-wrote a script there to do the folder structure listing & counting the specific files I was looking for.

The Powershell script to do that was:

$fldr = "C:\MyData"

Get-ChildItem $fldr -Recurse -Filter "*.txt" | where {!$_.PSIsContainer} | Group Directory |
Format-Table Name, Count -AutoSize |Write-Output | out-file "c:\Users\ThisIsMe\My Documents\TextFileCount.txt"

I simply imported the TextFileCount.txt file into Excel. Column B was the file count of the specific type of file I was looking for. From that point on, I did Vlookups of the average file size (for the MyData & YourData Images folders), multiplied those averages by the file counts & from then calculated the estimated size of the folders.

While I know the Powershell stuff isn't on-topic, a lot of the data I get from Powershell I use almost immediately in Excel, and posted the above in hopes that someone else may find it of some use. My apologies for making a twenty-five-cent problem into a Ten Dollar solution....