SUMIF formula required to search for 2 diff values in 2 diff colum

[Lidy693]

Hi
I have the following data:

A B C
Team Complaint Type Action Required
Sales Staff Rude 1
Production Late Order 1
Sales Late Order 1
Accounts Staff Rude 1
Sales Staff Rude 1

I would like to be able to sum the action required (column C) when the team
(column A) is "Sales" and the complaint type (column B) is "Staff Rude", the
answer should be 2. The formula I am working with is:
=sumif(a:b,"sales" "staff rude",c:c)

But I keep getting error messages - can anyone help?

thanks Lidy

 

[Mike H]

Try this

=SUMPRODUCT((A2:A29="Sales")*(B2:B29="Staff Rude"))

Mike

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A2:A6="Sales"),--(B2:B6="Staff Rude"),C2:C6)

Better to use cells to hold the criteria:

E1 = Sales
F1 = Staff Rude

=SUMPRODUCT(--(A2:A6=E1),--(B2:B6=F1),C2:C6)

Note that with SUMPRODUCT you can't use entire columns as range references
unless you're using Excel 2007. And, if you are using Excel 2007 I'd use
this formula:

=SUMIFS(C:C,A:A,E1,B:B,F1)

 

[Lidy693]

Thanks Mike - that works a treat, much appreciated

 

[Lidy693]

Thanks Biff, i'm so relieved now that i can calculate these totals, much
appreciated

 

[Mike H]

Hi,

Your welcome, glad i could help. I have a question, as soon as I saw Biff's
response I was convinced I had misunderstood the question and we should be
summing the ones in column C (which be 2 or 3) which his formula does, what
is the purpose of column C?

Mike

 

[Shane Devenshire]

Hi,

In 2007 you can use

=COUNTIFS(A:A,"Sales",B:B,"Staff Rude")
or better yet put Sales in D1 and Staff Rude in D2:
=COUNTIFS(A:A,D1,B:B,D2)

or in 2003 variation on the previous suggestions:
=SUMPRODUCT((A:A=D1)*(B:B=D2))

 

[T. Valko]

You're welcome. Thanks for the feedback!