|
Lookups and variable sums
Hey all. This will probably be an easy one for the experts, since I am
probably thinking too much. Here is my dilemma: I have a query through office connector that pulls information from one software package into excel. Each time this query is run, the size of the data may vary (ie there may be 8000 rows one time and 12000 the next). So here is the specifics: column a lists the time entries for each employee during a specific time period, like monthly or quarterly. I want to sum the values in column d for each employee. Example: Column A Column B Column C Column D Column E JOHNDOE01 20 Hours 15/Hour 300.00 SUMIF Formula JOHNDOE01 35 Hours 15/Hour 525.00 SUMIF Formula JOHNDOE01 25 Hours 15/Hour 375.00 SUMIF Formula JOHNDOE01 25 Hours 15/Hour 375.00 SUMIF Formula JANEDOE01 25 Hours 18/Hour 450.00 SUMIF Formula JANEDOE01 30 Hours 18/Hour 540.00 SUMIF Formula JANEDOE01 10 Hours 18/Hour 180.00 SUMIF Formula =SUMIF(A2:A15485,LOOKUP(A2:A15485,Sheet3!A2:A155), D2:D15485) Sheet3 has the master list of employees I put the formula in E1, then drug the formula down. As I did this, the formula changed to: =SUMIF(A3:A15486,LOOKUP(A3:A15486,Sheet3!A3:A156), G3:G15486) and so on. Is there a way I can code Column E without having to cut and paste to adjust the formula for each row? Thanks. |
Lookups and variable sums
This might be a silly question but why not just do a pivot table. Your data
is perfect for this and it will get you around the whole formula problem. Try this... Choose Data-Pivot Table and just follow the instructions Place the names in the right hand column and the amounts in the middle... "brentm" wrote: Hey all. This will probably be an easy one for the experts, since I am probably thinking too much. Here is my dilemma: I have a query through office connector that pulls information from one software package into excel. Each time this query is run, the size of the data may vary (ie there may be 8000 rows one time and 12000 the next). So here is the specifics: column a lists the time entries for each employee during a specific time period, like monthly or quarterly. I want to sum the values in column d for each employee. Example: Column A Column B Column C Column D Column E JOHNDOE01 20 Hours 15/Hour 300.00 SUMIF Formula JOHNDOE01 35 Hours 15/Hour 525.00 SUMIF Formula JOHNDOE01 25 Hours 15/Hour 375.00 SUMIF Formula JOHNDOE01 25 Hours 15/Hour 375.00 SUMIF Formula JANEDOE01 25 Hours 18/Hour 450.00 SUMIF Formula JANEDOE01 30 Hours 18/Hour 540.00 SUMIF Formula JANEDOE01 10 Hours 18/Hour 180.00 SUMIF Formula =SUMIF(A2:A15485,LOOKUP(A2:A15485,Sheet3!A2:A155), D2:D15485) Sheet3 has the master list of employees I put the formula in E1, then drug the formula down. As I did this, the formula changed to: =SUMIF(A3:A15486,LOOKUP(A3:A15486,Sheet3!A3:A156), G3:G15486) and so on. Is there a way I can code Column E without having to cut and paste to adjust the formula for each row? Thanks. |
Lookups and variable sums
If you want your formulas to work look up these two items
Absolulte References or better yet named ranges... HTH "brentm" wrote: Hey all. This will probably be an easy one for the experts, since I am probably thinking too much. Here is my dilemma: I have a query through office connector that pulls information from one software package into excel. Each time this query is run, the size of the data may vary (ie there may be 8000 rows one time and 12000 the next). So here is the specifics: column a lists the time entries for each employee during a specific time period, like monthly or quarterly. I want to sum the values in column d for each employee. Example: Column A Column B Column C Column D Column E JOHNDOE01 20 Hours 15/Hour 300.00 SUMIF Formula JOHNDOE01 35 Hours 15/Hour 525.00 SUMIF Formula JOHNDOE01 25 Hours 15/Hour 375.00 SUMIF Formula JOHNDOE01 25 Hours 15/Hour 375.00 SUMIF Formula JANEDOE01 25 Hours 18/Hour 450.00 SUMIF Formula JANEDOE01 30 Hours 18/Hour 540.00 SUMIF Formula JANEDOE01 10 Hours 18/Hour 180.00 SUMIF Formula =SUMIF(A2:A15485,LOOKUP(A2:A15485,Sheet3!A2:A155), D2:D15485) Sheet3 has the master list of employees I put the formula in E1, then drug the formula down. As I did this, the formula changed to: =SUMIF(A3:A15486,LOOKUP(A3:A15486,Sheet3!A3:A156), G3:G15486) and so on. Is there a way I can code Column E without having to cut and paste to adjust the formula for each row? Thanks. |
Lookups and variable sums
Jim,
Thanks for your input. I just started doing my own programming in Excel last week, so I am still very green. I think the pivot table will be the right solution. Thanks for the guidence. Brent Mercer "Jim Thomlinson" wrote: This might be a silly question but why not just do a pivot table. Your data is perfect for this and it will get you around the whole formula problem. Try this... Choose Data-Pivot Table and just follow the instructions Place the names in the right hand column and the amounts in the middle... "brentm" wrote: Hey all. This will probably be an easy one for the experts, since I am probably thinking too much. Here is my dilemma: I have a query through office connector that pulls information from one software package into excel. Each time this query is run, the size of the data may vary (ie there may be 8000 rows one time and 12000 the next). So here is the specifics: column a lists the time entries for each employee during a specific time period, like monthly or quarterly. I want to sum the values in column d for each employee. Example: Column A Column B Column C Column D Column E JOHNDOE01 20 Hours 15/Hour 300.00 SUMIF Formula JOHNDOE01 35 Hours 15/Hour 525.00 SUMIF Formula JOHNDOE01 25 Hours 15/Hour 375.00 SUMIF Formula JOHNDOE01 25 Hours 15/Hour 375.00 SUMIF Formula JANEDOE01 25 Hours 18/Hour 450.00 SUMIF Formula JANEDOE01 30 Hours 18/Hour 540.00 SUMIF Formula JANEDOE01 10 Hours 18/Hour 180.00 SUMIF Formula =SUMIF(A2:A15485,LOOKUP(A2:A15485,Sheet3!A2:A155), D2:D15485) Sheet3 has the master list of employees I put the formula in E1, then drug the formula down. As I did this, the formula changed to: =SUMIF(A3:A15486,LOOKUP(A3:A15486,Sheet3!A3:A156), G3:G15486) and so on. Is there a way I can code Column E without having to cut and paste to adjust the formula for each row? Thanks. |
| All times are GMT +1. The time now is 03:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com