SUMIF and grouped criteria

HI,

I am a moderate user of MS Excel and currenclty use MS Excel 2010 and MS Excel 2013.


Recently I work on a file which will sum up some amounts according to criterion.


My problem an case:

I use sumif function to sum up values according to criteria. These criteria are name sof departments: Sales, Marketing, Corporate Affairs' Finance, General Administration, Human Resources, IT, Legal, Security.

the amounts are poste by other users by identifying specific department for which department the amount is allocated.

But my goal is to sum up values according to 3 criteria: Sales, Marketing and GENERAL. The last criteron must contain other departments except sales and marketing.

Answer:

Is there any solution to create a list which will be attached to criteria GENERAL and include those departments? or anything by which I will show to SUMIF function that GENERAL criteria includes other departments except MArketing and sales.

Problem exists because users must post according to eachd epartment and I must sumup directly by 3 criterion.

If needed I wil upload a sample file.

To sum everything except Sales and Marketing try SUMIFS like this

=SUMIFS(B:B,A:A,"<>Sales",A:A,"<>Marketing")

assuming depts in column A and amounts in B

.....OR...create a list of all the other depts, assuming that's in D2:D8 use this formula

=SUMPRODUCT(SUMIF(A:A,D2:D8,B:B))