Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF based on data in adjacent row
Hello,
I have a spreadsheet used almost like a timesheet, which I use to compare projected hours spent against acutal hours spent. The list of names is fairly long and can have the same name twice so I would like to create a summary report which groups the total hours spent for an idividual person. Example: In column 'A' I have each staff member billing their time to a different project. Column 'B' is the expected hours they would spend over a two day period, Column 'C' and 'D' are the actual hours spent in those two days and Column 'E' is the total of the actual hours spent by that person on that project. A B C D E 1 ABC 16 8 7 15 2 DEF 16 8 8 16 3 GHI 16 8 8 16 4 ABC 16 12 9 21 5 JKL 16 8 8 16 6 MNO 16 12 8 20 7 DEF 16 8 8 16 I would like to take this data and create a summary table that looks like this: A B C D E 1 ABC 32 20 16 36 2 DEF 32 16 16 32 3 GHI 16 8 8 16 4 JKL 16 8 8 16 5 MNO 16 12 8 20 Which is sort of the same as above, however, all of the time spent by 'ABC' is now consolidated into one row. My list is very long and I do not want to use filters or program in miles of "IF" statements so I was wondering if there was a certain type of "SUMIF" formula that would work. Please let me know. THANKS! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF based on data in adjacent row
Try a search on this newsgroup
http://groups.google.com/group/micro...lic.excel.misc using search terms "sumproduct double unary". It's ideal for this application. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF based on data in adjacent row
Assuming the data is on Sheet1
On Sheet 2, list the people in A2:An In B2 =SUMPRODUCT(--(Sheet1!$A$2:$A$200=$A2),Sheet1!$B$2:$B$200) copy across and down -- HTH RP "jcpotwor" wrote in message ... Hello, I have a spreadsheet used almost like a timesheet, which I use to compare projected hours spent against acutal hours spent. The list of names is fairly long and can have the same name twice so I would like to create a summary report which groups the total hours spent for an idividual person. Example: In column 'A' I have each staff member billing their time to a different project. Column 'B' is the expected hours they would spend over a two day period, Column 'C' and 'D' are the actual hours spent in those two days and Column 'E' is the total of the actual hours spent by that person on that project. A B C D E 1 ABC 16 8 7 15 2 DEF 16 8 8 16 3 GHI 16 8 8 16 4 ABC 16 12 9 21 5 JKL 16 8 8 16 6 MNO 16 12 8 20 7 DEF 16 8 8 16 I would like to take this data and create a summary table that looks like this: A B C D E 1 ABC 32 20 16 36 2 DEF 32 16 16 32 3 GHI 16 8 8 16 4 JKL 16 8 8 16 5 MNO 16 12 8 20 Which is sort of the same as above, however, all of the time spent by 'ABC' is now consolidated into one row. My list is very long and I do not want to use filters or program in miles of "IF" statements so I was wondering if there was a certain type of "SUMIF" formula that would work. Please let me know. THANKS! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF based on data in adjacent row
Say you put your summary list in Sheet2, same as your example;
Unique list of names starting in A2, In B2 enter: =SUMIF(Sheet1!$A$2:$A$100,$A2,Sheet1!B$2:B$100) And copy across to E2, then down as needed. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "jcpotwor" wrote in message ... Hello, I have a spreadsheet used almost like a timesheet, which I use to compare projected hours spent against acutal hours spent. The list of names is fairly long and can have the same name twice so I would like to create a summary report which groups the total hours spent for an idividual person. Example: In column 'A' I have each staff member billing their time to a different project. Column 'B' is the expected hours they would spend over a two day period, Column 'C' and 'D' are the actual hours spent in those two days and Column 'E' is the total of the actual hours spent by that person on that project. A B C D E 1 ABC 16 8 7 15 2 DEF 16 8 8 16 3 GHI 16 8 8 16 4 ABC 16 12 9 21 5 JKL 16 8 8 16 6 MNO 16 12 8 20 7 DEF 16 8 8 16 I would like to take this data and create a summary table that looks like this: A B C D E 1 ABC 32 20 16 36 2 DEF 32 16 16 32 3 GHI 16 8 8 16 4 JKL 16 8 8 16 5 MNO 16 12 8 20 Which is sort of the same as above, however, all of the time spent by 'ABC' is now consolidated into one row. My list is very long and I do not want to use filters or program in miles of "IF" statements so I was wondering if there was a certain type of "SUMIF" formula that would work. Please let me know. THANKS! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF based on data in adjacent row
Thanks for the tips...now is there any way to do this without creating a
second sheet, using the autofilters on the orignal data but still counting only the unique listings? Bascially I am looking for a formula that would could the number of entries in the autofilter list. "RagDyer" wrote: Say you put your summary list in Sheet2, same as your example; Unique list of names starting in A2, In B2 enter: =SUMIF(Sheet1!$A$2:$A$100,$A2,Sheet1!B$2:B$100) And copy across to E2, then down as needed. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "jcpotwor" wrote in message ... Hello, I have a spreadsheet used almost like a timesheet, which I use to compare projected hours spent against acutal hours spent. The list of names is fairly long and can have the same name twice so I would like to create a summary report which groups the total hours spent for an idividual person. Example: In column 'A' I have each staff member billing their time to a different project. Column 'B' is the expected hours they would spend over a two day period, Column 'C' and 'D' are the actual hours spent in those two days and Column 'E' is the total of the actual hours spent by that person on that project. A B C D E 1 ABC 16 8 7 15 2 DEF 16 8 8 16 3 GHI 16 8 8 16 4 ABC 16 12 9 21 5 JKL 16 8 8 16 6 MNO 16 12 8 20 7 DEF 16 8 8 16 I would like to take this data and create a summary table that looks like this: A B C D E 1 ABC 32 20 16 36 2 DEF 32 16 16 32 3 GHI 16 8 8 16 4 JKL 16 8 8 16 5 MNO 16 12 8 20 Which is sort of the same as above, however, all of the time spent by 'ABC' is now consolidated into one row. My list is very long and I do not want to use filters or program in miles of "IF" statements so I was wondering if there was a certain type of "SUMIF" formula that would work. Please let me know. THANKS! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF based on data in adjacent row
=SUBTOTAL(9,B:B)
-- HTH RP "jcpotwor" wrote in message ... Thanks for the tips...now is there any way to do this without creating a second sheet, using the autofilters on the orignal data but still counting only the unique listings? Bascially I am looking for a formula that would could the number of entries in the autofilter list. "RagDyer" wrote: Say you put your summary list in Sheet2, same as your example; Unique list of names starting in A2, In B2 enter: =SUMIF(Sheet1!$A$2:$A$100,$A2,Sheet1!B$2:B$100) And copy across to E2, then down as needed. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "jcpotwor" wrote in message ... Hello, I have a spreadsheet used almost like a timesheet, which I use to compare projected hours spent against acutal hours spent. The list of names is fairly long and can have the same name twice so I would like to create a summary report which groups the total hours spent for an idividual person. Example: In column 'A' I have each staff member billing their time to a different project. Column 'B' is the expected hours they would spend over a two day period, Column 'C' and 'D' are the actual hours spent in those two days and Column 'E' is the total of the actual hours spent by that person on that project. A B C D E 1 ABC 16 8 7 15 2 DEF 16 8 8 16 3 GHI 16 8 8 16 4 ABC 16 12 9 21 5 JKL 16 8 8 16 6 MNO 16 12 8 20 7 DEF 16 8 8 16 I would like to take this data and create a summary table that looks like this: A B C D E 1 ABC 32 20 16 36 2 DEF 32 16 16 32 3 GHI 16 8 8 16 4 JKL 16 8 8 16 5 MNO 16 12 8 20 Which is sort of the same as above, however, all of the time spent by 'ABC' is now consolidated into one row. My list is very long and I do not want to use filters or program in miles of "IF" statements so I was wondering if there was a certain type of "SUMIF" formula that would work. Please let me know. THANKS! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF based on data in adjacent row
Thanks again for the tip but it still does not work. Let me try a more
simpler spreadsheet. Below is a list which I use the autofilters for. I want to count the number of names without counting the duplicates, I also want the number to update if I autofiltered the list by a specific project. Name Project Joe 1 Jim 2 Bob 4 Sam 4 Joe 5 Sam 4 Rob 7 Jimmy 8 Joe 9 So the count should at this point be equal to 6 for each individual name. I also want to use the auto filter so that they value will update. So if I filtered the list for project 4: Name Project Bob 4 Sam 4 Sam 4 That same formula would net a new value of 2. Is this possible? Thanks again for all of your help! "Bob Phillips" wrote: =SUBTOTAL(9,B:B) -- HTH RP "jcpotwor" wrote in message ... Thanks for the tips...now is there any way to do this without creating a second sheet, using the autofilters on the orignal data but still counting only the unique listings? Bascially I am looking for a formula that would could the number of entries in the autofilter list. "RagDyer" wrote: Say you put your summary list in Sheet2, same as your example; Unique list of names starting in A2, In B2 enter: =SUMIF(Sheet1!$A$2:$A$100,$A2,Sheet1!B$2:B$100) And copy across to E2, then down as needed. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "jcpotwor" wrote in message ... Hello, I have a spreadsheet used almost like a timesheet, which I use to compare projected hours spent against acutal hours spent. The list of names is fairly long and can have the same name twice so I would like to create a summary report which groups the total hours spent for an idividual person. Example: In column 'A' I have each staff member billing their time to a different project. Column 'B' is the expected hours they would spend over a two day period, Column 'C' and 'D' are the actual hours spent in those two days and Column 'E' is the total of the actual hours spent by that person on that project. A B C D E 1 ABC 16 8 7 15 2 DEF 16 8 8 16 3 GHI 16 8 8 16 4 ABC 16 12 9 21 5 JKL 16 8 8 16 6 MNO 16 12 8 20 7 DEF 16 8 8 16 I would like to take this data and create a summary table that looks like this: A B C D E 1 ABC 32 20 16 36 2 DEF 32 16 16 32 3 GHI 16 8 8 16 4 JKL 16 8 8 16 5 MNO 16 12 8 20 Which is sort of the same as above, however, all of the time spent by 'ABC' is now consolidated into one row. My list is very long and I do not want to use filters or program in miles of "IF" statements so I was wondering if there was a certain type of "SUMIF" formula that would work. Please let me know. THANKS! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Sharing data across worksheets within a workbook based on identifi | Excel Discussion (Misc queries) | |||
Macro to move data to different column based on data in another co | Excel Discussion (Misc queries) | |||
Adding total dollars based on specific data from another column | Excel Discussion (Misc queries) | |||
Importing Data Into Formula Based Cells | Excel Worksheet Functions |