Multiple Sheets and Columns

[Tom D]

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)

 

[T. Valko]

Try something like this:

=SUMPRODUCT(--(Details!$F2:$F105=A$10),--(Details!$G2:$G105=some_module),Details!$E2:$E107)

Biff

 

[T. Valko]

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

 

[Tom D]

I replace some_module with what I am looking for, correct?

 

[Tom D]

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]

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]

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]

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]

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]

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