A dynamic SumIfs formula

[gheyman]

I need a formula that where the column its summing (Sum_Range) is dynamic. It will be based on what the user selects. I have UserNames down column A. The formula would have to do a SumIf where it finds "Joe" in column A and Sum a column based on another criteria.

I have 5 Columns. The first column is a name. The next 4 columns are titles 1-4, 5-9, 10-24, 25-59 & 60-100
If the User enters "Rick" in Cell A1 and "5-9" in B2 then I need the sumif to total down "5-9" where column A equals Rick.
But I cant do a bunch of Nested If's because I have numerous columns. it doesn't only go to 60-100 I don't know if a type of "Match" formula can be added so it knows which column to add.

 

[Fluff]

If you use SUM on a filtered range it will NOT ignore hidden rows, therefore it will not give the same value as the formula.
To check the formula is correct you need to use SUBTOTAL on the filtered range.