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

L

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
 
T

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)
 
L

Lidy693

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

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
 
S

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top