Multiple OR criteria in argument

[Frankenstein]

Hello everyone,

Hoping someone can make quick work of this. I have a list of Manager titles and a list of Director titles. I'd like to search a database on a separate sheet and calculate two sums. One for all "manager titles" and one for all "Director" titles.

Typically i'd use a pivot table or the formula: =sum(sumif(Sheet2!B3:B15, {"Sales Manager", "Shipping Manager", "..."}, Sheet2!C3:C15) and call it a day. I have 2 problems with this:

1. My data is not tabular
2. My list of manager titles is much longer than this
3. I plan to use this formula in 10-20 cells, which if I ever need to add or remove criteria will be a huge PITA so I want all formulas to reference one list of titles.

A few things i have tried to give you an idea of my goal:

1. I changed D2 to {"Sales Manager", "Shipping Manager", "Human Resources Mgr", "Finance Mgr"} and then used: =sum(sumif(Sheet2!B3:B15, indirect(D2), Sheet2!C3:C15) and tried =sum(sumif(Sheet2!B3:B15, D2, Sheet2!C3:C15)
2. Changed D2 to "Sales Manager", "Shipping Manager", Human Resources Mgr", "Finance Mgr" and then used concatenation: =sum(sumif(Sheet2!B3:B15, "{"&D2&"}", Sheet2!C3:C15)

Thanks everyone!

 

[DanteAmor]

Hi and welcome to MrExcel.

Try:

=SUM(SUMIF(Sheet2!B3:B15,{"Sales Manager","Shipping Manager","Human Resources Mgr","Finance Mgr"},Sheet2!C3:C15))

Or:

=SUM(IF(Sheet2!B3:B15=TRIM(TEXTSPLIT(Sheet1!D2,",")),Sheet2!C3:C15,0))

 

[awoohaw]

Try this, maybe:
I have the data on same sheet, but cut and paste it to another workbook and it will work.
I suggest doing this in a fresh workbook first, so you can understand the concept before moving it into your work.

Book1
	A	B	C	D	E	F	G
1
2			Manager	Sales Manager, Shipping Manager, Human Resources Manager, Finance Manager
3			Director	Senior Director, Admin Director, Assoc Director
4
5
6			Sales Manager	10
7			Shipping Manager	10
8			Human Resources Manager	27
9			Finance Manager	22
10			Senior Director	22
11			Admin Director	22
12			Assoc Director	33
13
14
15
16
17
18				Sales Manager	10
19				Shipping Manager	10
20				Human Resources Manager	14
21				Finance Manager	11
22				Senior Director	11
23				Admin Director	12
24				Assoc Director	10
25				Human Resources Manager	13
26				Finance Manager	11
27				Senior Director	11
28				Admin Director	10
29				Assoc Director	13
30				Assoc Director	10
31
Sheet6