Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to add data from cells based on different criterias?
Hello,
First I just want to thank you for help in adavace. Well the issue that I'm having in trying to add up the amount time I have listed in cells L6:L50 based on two different criterias first I have the date in cells A6:A50 (sometime these dates repeat) and also the type of machinery that the time was spent on which I have in cell B6:B50 (sometimes the machine name repeats) all of which are in the same sheet (RTF). In between those columns I have other data that is required to be there but the ones I'm working with are the ones I specified. I'm trying to place the total amount of time in a different sheet named (Daily Time Analysis). On this sheet the layout is like this: In Cells A3:A13 I have the machine names and in Column and cell number B2:U2 I have the dates. I'm trying to have the amount of time under the exact date that the machine was worked on next to it's machine name. Example Of what i'm working with: Sheet) RTF Column A Column B Column L 2/1/2010 CNC#4 2:35 2/1/2010 CNC#1 3:20 2/1/2010 CNC#2 1:45 2/1/2010 CNC#1 2:15 2/1/2010 CNC#4 3:15 2/2/2010 CNC#3 0:45 2/2/2010 CNC#2 1:45 2/2/2010 CNC#3 2:35 2/2/2010 CNC#2 1:00 2/3/2010 CNC#4 0:35 2/3/2010 CNC#3 1:58 2/3/2010 CNC#4 2:55 Sheet) Daily Time Analysis Column A Column B Column C Column D Column E Column F 2/1/2010 2/2/2010 2/3/2010 2/4/2010 2/5/2010 CNC#1 CNC#2 CNC#3 CNC#4 One thing I have to add is that I need this formula to work with the dates that change every month so I don't have to create the formula over and over. I've combine the cell with date and machine name but it's a pain doing that for every day and using =SUMIF('RTF!C6:C50,"CNC#1 2/1/2010",RTF!L6:L50) but i then have to change the name and date too. I hope I'm getting my point of what I need to do across. Let me know if I don't. Thanks Again Peter |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to add data from cells based on different criterias?
HI Peter,
Insert this formula in B2 in Daily Time Analysis sheet and copy it to other cells: =SUMPRODUCT(--(RTF!$A$6:$A$50=Sheet2!B$1),--(RTF!$C$6:$C$50=Sheet2! $A2),RTF!$L$6:$L$50) Hopes this helps. .... Per On 6 Feb., 00:13, Peter Gonzalez wrote: Hello, First I just want to thank you for help in adavace. Well the issue that I'm having in trying to add up the amount time I have listed in cells L6:L50 based on two different criterias first I have the date in cells A6:A50 (sometime these dates repeat) and also the type of machinery that the time was spent on which I have in cell B6:B50 (sometimes the machine name repeats) all of which are in the same sheet (RTF). In between those columns I have other data that is required to be there but the ones I'm working with are the ones I specified. I'm trying to place the total amount of time in a different sheet named (Daily Time Analysis). On this sheet the layout is like this: In Cells A3:A13 I have the machine names and in Column and cell number B2:U2 I have the dates. I'm trying to have the amount of time under the exact date that the machine was worked on next to it's machine name. Example Of what i'm working with: Sheet) RTF Column A *Column B * *Column L 2/1/2010 * *CNC#4 * * *2:35 2/1/2010 * *CNC#1 * * *3:20 2/1/2010 * *CNC#2 * * *1:45 2/1/2010 * *CNC#1 * * *2:15 2/1/2010 * *CNC#4 * * *3:15 2/2/2010 * *CNC#3 * * *0:45 2/2/2010 * *CNC#2 * * *1:45 2/2/2010 * *CNC#3 * * *2:35 2/2/2010 * *CNC#2 * * *1:00 2/3/2010 * *CNC#4 * * *0:35 2/3/2010 * *CNC#3 * * *1:58 2/3/2010 * *CNC#4 * * *2:55 Sheet) Daily Time Analysis Column A * *Column B * Column C *Column D * Column E * Column F * * * * * * * * * 2/1/2010 * *2/2/2010 * 2/3/2010 * *2/4/2010 * * 2/5/2010 CNC#1 CNC#2 CNC#3 CNC#4 One thing I have to add is that I need this formula to work with the dates that change every month so I don't have to create the formula over and over. I've combine the cell with date and machine name but it's a pain doing that for every day and using =SUMIF('RTF!C6:C50,"CNC#1 2/1/2010",RTF!L6:L50) but i then have to change the name and date too. I hope I'm getting my point of what I need to do across. Let me know if I don't. Thanks Again Peter |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to add data from cells based on different criterias?
Excel 2007 PivotTable, PivotChart
No formulas needed. http://www.mediafire.com/file/ouztm1tmtxt/02_06_10.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum based on 4 criterias | Excel Discussion (Misc queries) | |||
Conditional Formatting Based of Cells Based on Data Entry in anoth | Excel Discussion (Misc queries) | |||
Insert values based on 2 criterias | Excel Discussion (Misc queries) | |||
Counting instances based on two criterias | Excel Worksheet Functions | |||
Delete rows based on multiple criterias | Excel Discussion (Misc queries) |