Trying to figure out how to create a cascading filter formula.

Hi.. first time using this forum.. some background: I have a really large dataset with common medical errors in all 50 states sorted by year. My goal is to have the spreadsheet work so that you enter a year in a cell on Sheet1 and it tells you how many of each type of medical error occurred in each state in that year.

I've already set it up so that if you enter a year it tells you the number of overall errors by using a countif function. I also have a separate count which returns the number of total errors in each state by running a formula on a separate sheet called "MALSTATE" which utilizes an IF function to return the state if and only if the year in the column for year (in this case E) on the sheet titled "Data" matches the year entered into the input cell on the sheet titled "First"

{=IF(Data!W2=First!$A$2,Data!J2)}

And then there's another set of countifs which counts the number of errors in each state, i.e.=

{=COUNTIF(C2:C383466,"MA")}

So here's where I'm stuck.. I want to have a formula which ranks the practice areas which have the most errors, and which types of errors occur most frequently.

I guess I'm leaning towards creating a pivot table but I was wondering if there were any other ways to do this?