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

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

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

Try this

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

Mike


"Lidy693" wrote:

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

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

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)

--
Biff
Microsoft Excel MVP


"Lidy693" wrote in message
...
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

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

Thanks Mike - that works a treat, much appreciated
--
Lidy


"Mike H" wrote:

Try this

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

Mike


"Lidy693" wrote:

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

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

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


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Lidy693" wrote in message
...
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

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

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

"Lidy693" wrote:

Thanks Mike - that works a treat, much appreciated
--
Lidy


"Mike H" wrote:

Try this

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

Mike


"Lidy693" wrote:

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

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

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

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Lidy693" wrote:

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

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lidy693" wrote in message
...
Thanks Biff, i'm so relieved now that i can calculate these totals, much
appreciated
--
Lidy


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Lidy693" wrote in message
...
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