Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
which formula to use
I have data in 3 different columns and want to use criteria from column A and
B and to give me the result of column C in a different worksheet. e.g A B C Finance Debbie 10 IT Bill 20 Finance Debbie 15 Finance Angie 30 I want to gather the total amount (C) of all Finance (A) Debbie (B) to a different worksheet. What formula do I use? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
which formula to use
--When you have multiple criteria use SUMPRODUCT()
=SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2), C1:C10) In Sheet2 cell A1 = Finance and B1 = Debbie..In Sheet2 try the below formula =SUMPRODUCT(--(Sheet1!$A$1:$A$100=A1), --(Sheet1!$B$1:$B$100=B1),Sheet1!$C$1:$C$100) If you are using Excel 2007 you can use SUMIFS() to acheive the same result =SUMIFS(C1:C10,A1:A10,criteria1,B1:B10,criteria2) 'or with cells F1 and F2 holding the criteria =SUMIFS(C1:C10,A1:A10,F1,B1:B10,F2) If this post helps click Yes --------------- Jacob Skaria "DINAKI" wrote: I have data in 3 different columns and want to use criteria from column A and B and to give me the result of column C in a different worksheet. e.g A B C Finance Debbie 10 IT Bill 20 Finance Debbie 15 Finance Angie 30 I want to gather the total amount (C) of all Finance (A) Debbie (B) to a different worksheet. What formula do I use? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
which formula to use
Use sumproduct =sumproduct((a2:a22="finance")*(b2:b22="debbie")*c 2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software "DINAKI" wrote in message ... I have data in 3 different columns and want to use criteria from column A and B and to give me the result of column C in a different worksheet. e.g A B C Finance Debbie 10 IT Bill 20 Finance Debbie 15 Finance Angie 30 I want to gather the total amount (C) of all Finance (A) Debbie (B) to a different worksheet. What formula do I use? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
which formula to use
This sounds like a perfect situation to learn about pivottables--especially if
you need all the data for all departments and names. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx DINAKI wrote: I have data in 3 different columns and want to use criteria from column A and B and to give me the result of column C in a different worksheet. e.g A B C Finance Debbie 10 IT Bill 20 Finance Debbie 15 Finance Angie 30 I want to gather the total amount (C) of all Finance (A) Debbie (B) to a different worksheet. What formula do I use? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
which formula to use
Or you might try a pivot table, which will do that sort of summation
automatically if you arrange your fields correctly. Select all of your data. Then select the menu "Data/Pivot Table and Pivot Chart Report...". Then go through the steps to create the pivot table, arranging your data as you desire. HTH, Eric "Jacob Skaria" wrote: --When you have multiple criteria use SUMPRODUCT() =SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2), C1:C10) In Sheet2 cell A1 = Finance and B1 = Debbie..In Sheet2 try the below formula =SUMPRODUCT(--(Sheet1!$A$1:$A$100=A1), --(Sheet1!$B$1:$B$100=B1),Sheet1!$C$1:$C$100) If you are using Excel 2007 you can use SUMIFS() to acheive the same result =SUMIFS(C1:C10,A1:A10,criteria1,B1:B10,criteria2) 'or with cells F1 and F2 holding the criteria =SUMIFS(C1:C10,A1:A10,F1,B1:B10,F2) If this post helps click Yes --------------- Jacob Skaria "DINAKI" wrote: I have data in 3 different columns and want to use criteria from column A and B and to give me the result of column C in a different worksheet. e.g A B C Finance Debbie 10 IT Bill 20 Finance Debbie 15 Finance Angie 30 I want to gather the total amount (C) of all Finance (A) Debbie (B) to a different worksheet. What formula do I use? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|