Help to reduce SUMIFS array calc time & include additional variable to large formula

Hi all,

I have a working SUMIFS array formula, however due to the size and work excel has to do to calculate the formula it takes upto a minute to execute the drop down change.

My first question is, is there a way to simplify the formula?

The formula explained: If Layout:J2 = 9 then it's a subset of California and needs to pull all set variable data including anything flagged as "Ticket" in Column J of the Raw Data tab

If the right of column B in Data tab = ries, or lays, or ices, or lies they are on DVD so need to reference the DVD column in col E of Layout Tab
If the right last 4 digits of column B in Data tab = ions, or arts, or uite they are in the Cinema so need to reference the Cinema column in col F of Layout Tab
Otherwise they are a Tier 1 customer and need to reference column D of the Layout tab

A variable I haven't figured out is how to sum all of United States of A, as in column A there are often States and data which we need to keep in the report, I was trying to not further add to the complexity of the formula.

In the future I would like to be able to add time drop down functionality to select the data by month.

Thanks for anyone that may be able to help