Function similar to "Sumif"

I have Categories (eg. A, N, W, CC, 695, 40, 44...etc) in Sheet1 Column B. I have Amounts (eg. numbers) in Sheet1 Column C. **See ATTACHED**

I would like a function that will take numerous Categories as inputs and will return the sum of all the Category amounts combined.

For instance, if I were to type the following formula into ANY cell in the workbook. Here would be the results:

Please Login or Register  to view this content.

***The Categories will ALWAYS be in Sheet1 ColumnB and the amounts will ALWAYS be in Sheet1 ColumnC***
***I would like this function to work regardless of the sheet it is entered into in the workbook**

Xx7,

To use specified ranges from anywhere in a workbook, you should setup a dynamic named ranges.

For the Categories, create a named range and call it rngCategories and use this formula:

Please Login or Register  to view this content.

For the Amounts, create a named range and call it rngAmounts and use this formula:

Please Login or Register  to view this content.

Then, anywhere in the workbook, you can put in this formula to get the desired results:

Please Login or Register  to view this content.

Change the part in blue to whatever you'd like

Xx7,

If native excel formulas aren't an option, here's a UDF that will perform as requested. Make sure it is placed in a standard module within the workbook that will need its functionality:

Please Login or Register  to view this content.

Here are some examples for how to use it:
=LEAD("A,U")
=LEAD("CC,61,54,43")
=LEAD("A,U,C,W,CC,KK,TT.1,695,40,54,43")

Whenever you open a file that contains macros, you'll have to tell Excel to enable macros, or the VBA code won't work. If you're not getting a prompt to enable macros when you open the workbook, you'll need to change your macro security settings in the trust center