|
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) |
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) |
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) |
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) |
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) |
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) |
Multiple Sheets and Columns
It sounds like you want an average per workday.
=Sum of column E / count of column A =SUM(E1:E100)/COUNT(A1:A100) Or am I not getting what you want? Biff "Tom D" wrote in message ... 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) |
Multiple Sheets and Columns
I am probably not making it clear...
I have all calendar days on the details page. I want to exclude non-workdays (which will vary by month) and only count the work days (so 22 work days vs 31 calendar days, for example) and then count the incidents in E and total them. Could I identify a non-work day with some letter (N, for example) and set a formula to ignore those days, get the oncs with numeric values and and count those? For example, in april, I want to skip the 7th,8th, 14th, 15th, 21st, 22nd, 28th, 29th, but not have to identify the specific dates, but only identify a characteristic. Does that make sense? "T. Valko" wrote: It sounds like you want an average per workday. =Sum of column E / count of column A =SUM(E1:E100)/COUNT(A1:A100) Or am I not getting what you want? Biff "Tom D" wrote in message ... 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) |
Multiple Sheets and Columns
OK, I understand...
If the dates you wanted to include were always Mon thru Fri you could do this without a helper column. If some dates to be excluded are "holidays" that fall on weekdays Mon thry Fri then you would have to make a separate list of those dates. So, to make things as simple as possible I think you should use an additional column and just identify those "holiday" dates with some kind of letter designation like "H". Assume: Column A = dates Column B = holiday indentifier. "H" means to exclude this date Column E = values to sum =SUMPRODUCT(--(WEEKDAY(A1:A20,2)<6),--(B1:B20=""),E1:E20) Biff "Tom D" wrote in message ... I am probably not making it clear... I have all calendar days on the details page. I want to exclude non-workdays (which will vary by month) and only count the work days (so 22 work days vs 31 calendar days, for example) and then count the incidents in E and total them. Could I identify a non-work day with some letter (N, for example) and set a formula to ignore those days, get the oncs with numeric values and and count those? For example, in april, I want to skip the 7th,8th, 14th, 15th, 21st, 22nd, 28th, 29th, but not have to identify the specific dates, but only identify a characteristic. Does that make sense? "T. Valko" wrote: It sounds like you want an average per workday. =Sum of column E / count of column A =SUM(E1:E100)/COUNT(A1:A100) Or am I not getting what you want? Biff "Tom D" wrote in message ... 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) |
| All times are GMT +1. The time now is 10:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com