SUMIF and the AND Function

[Mike]

I want to use the SUMIF function to evaluate two chatracteristics. My date looks something like this:

A B C D E F
1 Name Department Number Compensation Type Jan-04 Feb-04 Mar-04
2 John Smith 100000 Salary Or Wages 3,333 3,333 3,333
3 100000 Overtime Wages 0 0 0
4 100000 Bonus
5 100000 Commissions
6 100000 Incentives
7 Tom Brown 100000 Salary Or Wages 2,333 2,333 2,333
8 100000 Overtime Wages 0 0 0
9 100000 Bonus
10 100000 Commissions
11 100000 Incentives
12 Rob Green 200000 Salary Or Wages 1,000 1,000 1,000
13 200000 Overtime Wages 0 0 0
14 200000 Bonus
15 200000 Commissions
16 200000 Incentives

I want the formula to look at columns B and C. If coloumn B is equal to 100000 and column C is equal to Salary and Wages then I want the corresponding cell in Column D summed.

Thank you for your help

 

[Frank Kabel]

Hi Mike

try the following
=SUM((B2:B16=100000)*(C2:C16="Salary or Wages")*(D216))
you have to enter this as array formula (CTRL+SHIFT+ENTER)

Also some links
http://www.cpearson.com/excel/array.htm
http://www.j-walk.com/ss/excel/tips/tip74.htm

Frank

Mike wrote:
[example data]

 

[Bob Phillips]

or

=SUMPRODUCT((B2:B16=100000)*(C2:C16="Salary or Wages"),(D216))

which is not array entered

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi Mike

try the following
=SUM((B2:B16=100000)*(C2:C16="Salary or Wages")*(D216))
you have to enter this as array formula (CTRL+SHIFT+ENTER)

Also some links
http://www.cpearson.com/excel/array.htm
http://www.j-walk.com/ss/excel/tips/tip74.htm

Frank

Mike wrote:
[example data]

I want the formula to look at columns B and C. If coloumn B is equal
to 100000 and column C is equal to Salary and Wages then I want the
corresponding cell in Column D summed.

Thank you for your help