| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sheets and Columns
I have a sheet called Details that has 3 columns that I want to create a
summary for on another sheet (called summary). One column has different peoples names (F). Another column has different module names(G). The addtion column has a number in it (E). What I am trying to get is a summary of how many (E) each person (F) has per module (G). I have a formula that gets all the totals in one bucket, but whem I try to add the 'G' range, it tells me its too complex. I am pretty new to this... SUMIF(Details!$F2:$F105,A$10,Details!$E2:$E107) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheets and Columns
Try something like this:
=SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107) Biff "Tom D" <Tom wrote in message ... I have a sheet called Details that has 3 columns that I want to create a summary for on another sheet (called summary). One column has different peoples names (F). Another column has different module names(G). The addtion column has a number in it (E). What I am trying to get is a summary of how many (E) each person (F) has per module (G). I have a formula that gets all the totals in one bucket, but whem I try to add the 'G' range, it tells me its too complex. I am pretty new to this... SUMIF(Details!$F2:$F105,A$10,Details!$E2:$E107) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheets and Columns
Ooops!
=SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107) I just noticed that Details!$E2:$E107 is not the same size as the other references. *ALL* ranges must be the same size: =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E105) Biff "T. Valko" wrote in message ... Try something like this: =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107) Biff "Tom D" <Tom wrote in message ... I have a sheet called Details that has 3 columns that I want to create a summary for on another sheet (called summary). One column has different peoples names (F). Another column has different module names(G). The addtion column has a number in it (E). What I am trying to get is a summary of how many (E) each person (F) has per module (G). I have a formula that gets all the totals in one bucket, but whem I try to add the 'G' range, it tells me its too complex. I am pretty new to this... SUMIF(Details!$F2:$F105,A$10,Details!$E2:$E107) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheets and Columns
I replace some_module with what I am looking for, correct?
"T. Valko" wrote: Ooops! =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107) I just noticed that Details!$E2:$E107 is not the same size as the other references. *ALL* ranges must be the same size: =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E105) Biff "T. Valko" wrote in message ... Try something like this: =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107) Biff "Tom D" <Tom wrote in message ... I have a sheet called Details that has 3 columns that I want to create a summary for on another sheet (called summary). One column has different peoples names (F). Another column has different module names(G). The addtion column has a number in it (E). What I am trying to get is a summary of how many (E) each person (F) has per module (G). I have a formula that gets all the totals in one bucket, but whem I try to add the 'G' range, it tells me its too complex. I am pretty new to this... SUMIF(Details!$F2:$F105,A$10,Details!$E2:$E107) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheets and Columns
Okay, this works for what I need! Is there anyway to 'lock' the formula
against that creep when I cut and paste? I have to edit each one bck to F2, E2, etc. TD "T. Valko" wrote: Ooops! =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107) I just noticed that Details!$E2:$E107 is not the same size as the other references. *ALL* ranges must be the same size: =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E105) Biff "T. Valko" wrote in message ... Try something like this: =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107) Biff "Tom D" <Tom wrote in message ... I have a sheet called Details that has 3 columns that I want to create a summary for on another sheet (called summary). One column has different peoples names (F). Another column has different module names(G). The addtion column has a number in it (E). What I am trying to get is a summary of how many (E) each person (F) has per module (G). I have a formula that gets all the totals in one bucket, but whem I try to add the 'G' range, it tells me its too complex. I am pretty new to this... SUMIF(Details!$F2:$F105,A$10,Details!$E2:$E107) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheets and Columns
Use absolute references:
=SUMPRODUCT(--(Details!$F$2:$F$105=A$10),--(Details!$G$2:$G$105=some_module),Details!$E$2:$E$ 105) Biff "Tom D" wrote in message ... Okay, this works for what I need! Is there anyway to 'lock' the formula against that creep when I cut and paste? I have to edit each one bck to F2, E2, etc. TD "T. Valko" wrote: Ooops! =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107) I just noticed that Details!$E2:$E107 is not the same size as the other references. *ALL* ranges must be the same size: =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E105) Biff "T. Valko" wrote in message ... Try something like this: =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107) Biff "Tom D" <Tom wrote in message ... I have a sheet called Details that has 3 columns that I want to create a summary for on another sheet (called summary). One column has different peoples names (F). Another column has different module names(G). The addtion column has a number in it (E). What I am trying to get is a summary of how many (E) each person (F) has per module (G). I have a formula that gets all the totals in one bucket, but whem I try to add the 'G' range, it tells me its too complex. I am pretty new to this... SUMIF(Details!$F2:$F105,A$10,Details!$E2:$E107) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheets and Columns
Thanks, Biff.
Next, I am trying to quantify the # of 'working' days in a month (M-F) and items for those days. My details page has a date entry for the each day of the month. The dates are set from the 1st working day of themonth (in april, this is 04/02/2007 occupies cell A1), +1. =A1+1 gets me the 4/03/2007 for Cell A10, =A10+1 gets me the 4th for Cell A21, etc. Some days, we are closed, other days, we may get nothing (sum 0). I want to be able to calculate the number of working days in the month and then divide the total of 'incidents' in the E column, including the working days that had no entries. Does that make sense? Each Date has like 10 entries below before going to the next Date. Each of those entries potential will have a 1 in the E column. "T. Valko" wrote: Use absolute references: =SUMPRODUCT(--(Details!$F$2:$F$105=A$10),--(Details!$G$2:$G$105=some_module),Details!$E$2:$E$ 105) Biff "Tom D" wrote in message ... Okay, this works for what I need! Is there anyway to 'lock' the formula against that creep when I cut and paste? I have to edit each one bck to F2, E2, etc. TD "T. Valko" wrote: Ooops! =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107) I just noticed that Details!$E2:$E107 is not the same size as the other references. *ALL* ranges must be the same size: =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E105) Biff "T. Valko" wrote in message ... Try something like this: =SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107) Biff "Tom D" <Tom wrote in message ... I have a sheet called Details that has 3 columns that I want to create a summary for on another sheet (called summary). One column has different peoples names (F). Another column has different module names(G). The addtion column has a number in it (E). What I am trying to get is a summary of how many (E) each person (F) has per module (G). I have a formula that gets all the totals in one bucket, but whem I try to add the 'G' range, it tells me its too complex. I am pretty new to this... SUMIF(Details!$F2:$F105,A$10,Details!$E2:$E107) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automated multiple text files into multiple sheets in one workbook | Excel Discussion (Misc queries) | |||
| Comparing multiple columns in two sheets | Excel Worksheet Functions | |||
| Can columns be hidden accross multiple sheets in an Excel workboo | Excel Worksheet Functions | |||
| Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
| macro to compile columns on multiple sheets | Excel Discussion (Misc queries) | |||
![Tom D[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
