SUMIFS criteria based on a manual table

[TheMegaSage]

I am creating a template file with formulas so it can be easily shared out across departments. There are a number of categories (such as Staffing, Software, Outsourcing, etc) on this template. I am looking up from a data dump, and there is a column that will contain different (what we call) voucher text. I want to create a table with the common "voucher text" in one column and then a second column that shows the category to which it belongs.

For example, one voucher text will say "Salary GL Code for 590 J99112530-1", and another will say "Salary GL Code for 591 J99112530-1". Each department has its own three digit code (590, 591, etc), and the rest of the text will change each month, but it will always start with "Salary GL Code" ...

I want to create a table that says:

Term	Category
Salary GL Code*	Staffing

Then in the SUMIFS, I want to use a criteria that does a search on the custom table so when it finds a row with Salary GL Code, it returns the Category to make sure the cell I am summing is in the proper category.

If I hardcode the search criteria, it works, so as a 'brute force' method I'm doing a + to multiple SUMIFS formulas. Example:

=SUMIFS('[Data.xlsx]Data'!$Q:$Q, '[Data.xlsx]Data'!$C:$C,$B$1, '[Data.xlsx]Data'!$S:$S,$B$2, '[Data.xlsx]Data'!$P:$P,"*VACATION ACCRUAL")
+SUMIFS('[Data.xlsx]Data'!$Q:$Q, '[Data.xlsx]Data'!$C:$C,$B$1, '[Data.xlsx]Data'!$S:$S,$B$2, '[Data.xlsx]Data'!$P:$P,"Salary GL Code*")

Any thoughts about how to get away from the need to hardcode?

 

[Crystalyzer]

It would be extremely helpful if you could post a copy of your worksheet using the XL2BB tool.