Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how you make links between diff. cells on diff. work sheets | Excel Worksheet Functions | |||
Neg Values in Chart with Diff Color? | Excel Discussion (Misc queries) | |||
Adding values from 1 col based on value in diff col | Excel Discussion (Misc queries) | |||
Merging multiple WS with diff values into one master | Excel Discussion (Misc queries) | |||
Count If 2 Diff Values Appear on Several Worksheets w/in Workbook | Excel Worksheet Functions |