Sum Col B IF Cells in A = This, or This, or This

[squidmark]

I have, in worksheet 1, a summarized Income Statement, by category. I have, in worksheet 2, the normal Income Statement. The rows in column A on the Normal Income Statement (hereafter known as Tab2) are like this, with the Account # and Account Name:
4010 - Subscription Revenue
4310 - Service Revenue
4330 - License Revenue
5330 - Bandwidth Expense
5440 - Maintenance Expense

and on, and on. With the dollar amounts in columns B, C, D, etc, for each month.

On the Summary Income Statement (hereafter known as Tab 1), I have another layout of the income statement that uses categories, rather than the general ledger accounts, i.e.
Recurring Revenues
Product Sales Revenues
Hosting Expenses

etc.

These categories are listed in column B

The "Recurring Revenues" category on Tab 1 should sum up accounts 4010 and 4330 from Tab 2. But exclude account 4310. There are about 50 rows of categories that sum up various accounts from the report on Tab 2.

I know I can do sumifs(Sheet2!B:B,Sheet2!$A:$A,"4010",Sheet2!$A:$A,"4330") and grab the numbers that way. But is there a way to do some sumif(s), and have all the criteria lumped into one string? Does that make sense? Some of the categories include up to 15 different accounts from the normal income statement, and if possible, I'd rather not have a formula that includes 15 different criteria and criteria ranges.

For each category in column B, I do have all the account numbers that should be included in that category in column A on the same row. But I can hard-code the values in the formula if easier, rather than referencing column A.

I hope that makes sense.

Thanks.

 

[greenman]

i have done something similar for payroll, the way i went around it was creating a key where in you can specify your Tab 1 catergory and then use sumif.

For example: 4010 - Subscription Revenue = Recurring Revenues
4330 - License Revenue = Recurring Revenues and so on. and you can insert a column in tab b which will vlooup the catergory for you.