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)