Help With A Formula To Calculate Only Filtered Data?

[artiststevens]

Hello,
This is a continuation of a thread I started a few days ago asking for assistance with creating a formula to transfer a portion of data from one tab to another in the same worksheet. The worksheet consists of two tabs, Original and Sort. I wanted to import a certain portion of data from the Original tab to the Sort tab and received help with a formula that did that exactly. Now I’m seeking help with the next step with what I am trying to do and that’s filter the data and calculate the filtered results.

Phase 2
First question I have regarding what I am trying to do is find out if it’s possible for Excel to calculate data that has been filtered using the filtered function. I want to be able to use the Excel’s filter to select a certain criteria of data and have a formula give me specific information about that filter and if I make changes it will give me information based upon the new filtering as well and so on. If it is indeed possible the following is what I would like to do, if not I would have to find other means. After the selected data has been transferred to the Sort tab I then what to filter the data between the columns with “Start” and “Finish” above them and have formulas that calculate results based upon those filters.

As in the example below:
The cell “I9” has been sorted to show only the value of “6” that appear in all corresponding rows. Those are the only rows that would be active and selected based upon the criteria entered.

After sorting, I then want a formula that list all the values that are showing in the “N9” (Values) and the number of times that a value appears and its percentage of the total number of values based on what remains after filtering in cell “O9” (Total: #/%). The tabulation will be done for all columns in the “Start” to “Finish” radius.

Starting with column “H”, in cell “H9”, the formula in “N10” would list all of the values listed in the column after the filtering of the value 6 in column “I”. In column “H” the only value listed is 1 and it appears three times. Since it’s the only value that means it is 100% of the values in that column, so in “O10” the result from the formula should be 3/100%. I would require a formula that could do that for all of the columns from “Start” to “Finish” in the “Value” column for each factor

I’m fine with the inclusion of helper columns or any additional formulas that are needed to achieve what I am trying to accomplish but would prefer a formula over VBA due to my lack of knowledge.

2A.jpg

Final note:
Please see the attached spreadsheet for more information and a deeper breakdown of what I am trying to accomplish with a couple of Sort tab examples of how the data should be decided calculated based upon the Original tab. Also the actually spreadsheet contains much more data and factors. So I need a solution that can calculate the formulations quickly. Everything in the attached spreadsheet is calculated and entered by hand so it may contain errors. Hopefully my breakdown of what I require is sufficient explanation. Ignore the portions in red for they are areas to be formulated later.

Thank you for any and all help you can provide it would be greatly appreciated. Please feel free to ask any and all questions.