| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookups and sums? | Excel Worksheet Functions | |||
| Formula that sums/counts across worksheets with variable reference | Excel Discussion (Misc queries) | |||
| How to add previous sums in a column to current sums in a column? | Excel Worksheet Functions | |||
| Multi-lookups on variable data | Excel Worksheet Functions | |||
| LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions | |||
Linear Mode
