An alternative could be:

=SUMPRODUCT(--(Schedule!$B3:$AF3="N")*(Schedule!$B2:$AF$2=({"M";"W";"F"}))*(Day(Schedule!$B1:$AF$1)<=17))+

SUMPRODUCT(--(Schedule!$S3:$AF3="N")*(Schedule!$S2:$AF$2=({"M";"T";"W";"TH";"F"}))*"}))*(Day(Schedule!$B1:$AF$1)>17)))

=SUMPRODUCT(--(Schedule!$B3:$AF3="N")*(Schedule!$B2:$AF$2=({"T";"Th";"Su"}))*"}))*(Day(Schedule!$B1:$AF$1)<=17))

This mkes it slightly easier to amend if the days schedule is modified.

HTH

"Toppers" wrote:

> The formula below will look in days 1-17 for M, W F and then days 18-31 for
> M,T,W,TH,F for FEV and add the results together.
>
> =SUMPRODUCT(--(Schedule!$B3:$R3="N")*(Schedule!$B2:$R$2=({"M";"W";"F"})))+
> SUMPRODUCT(--(Schedule!$S3:$AF3="N")*(Schedule!$S2:$AF$2=({"M";"T";"W";"TH";"F"})))
>
> Similarly, this will only look at days 1-17 for FAI
>
> =SUMPRODUCT(--(Schedule!$B3:$R3="N")*(Schedule!$B2:$R$2=({"T";"Th";"Su"})))
>
> So again .... is this what is required?
>
> If you are able to, post a sample w/book with expected results and l'll take
> a look.
>
> [toppers<at>johntopley.fsnet.co.uk]
>
> "yukon_phil" wrote:
>
> > The fields for the dates are B1:AF31 and the days are B2:AF2, does this help?
> >
> > "Toppers" wrote:
> >
> > > ASSUMING (always dangerous!) B2:AF2 represent the periods 1-31, then would
> > > this work?
> > >
> > > for YEV
> > > =SUMPRODUCT(--(Schedule!$B3:$R3="N")*(Schedule!$B2:$R$2=({"M";"W";"F"})))+
> > > =SUMPRODUCT(--(Schedule!$S3:$AF3="N")*(Schedule!$S2:$AF$2=({"M";"T";"W";"TH";"F"})))
> > >
> > > for FAI
> > >
> > > =SUMPRODUCT(--(Schedule!$B3:$R3="N")*(Schedule!$B2:$R$2=({"T";"Th";"Su"})))
> > >
> > > If not, then how do we identify what is 1-17 and 18-31, as this info needs
> > > be included as part of your formula.
> > >
> > > HTH
> > >
> > > "yukon_phil" wrote:
> > >
> > > > I will try to explain my calculation quandry;
> > > >
> > > > I have 3 spreadsheets;
> > > > 1 -"Schedule" identifes who is flying what routes on what day
> > > > 2-"Number of Shifts Detail" Summerizes the number of occurances of each
> > > > shift type for the month for each employee
> > > > 3-"Shift & Mileage Summary" Has the total number of each shift type and
> > > > applies the air miles to provide a total of miles flown by employee for the
> > > > month.
> > > >
> > > > Two Problems but will explain the first one here now;
> > > >
> > > > The flight designation "N" goes to YEV on M,W,F
> > > > Goes to FAI on T,Th,Su
> > > >
> > > > My "Number of Shift Details" has the Column "Q" as "YEV" & Column "R"
> > > > as"FAI" the formula in the column Q is;
> > > > =SUMPRODUCT(--(Schedule!$B3:$AF3="N")*(Schedule!$B2:$AF$2=({"M";"W";"F"})))
> > > >
> > > > The formula in the column R is:
> > > > =SUMPRODUCT(--(Schedule!$B3:$AF3="N")*(Schedule!$B2:$AF$2=({"T";"Th";"Su"})))
> > > >
> > > > and in the "Shift & Mileage Summary" sheet the formula in Column H is;
> > > >
> > > > ='Number of Shifts Detail'!Q4*1370+'Number of Shifts Detail'!R4*1290
> > > >
> > > > The Number 1370 and 1290 are the air miles to the destinations.
> > > >
> > > > This situation works when for the entire month this schedule is maintained.
> > > >
> > > > NOW to my problem.
> > > >
> > > > From the 1-17 the above schedule is active BUT from the 18-31 it changes to
> > > > the following:
> > > >
> > > > On M,T,W,Th,F it goes only to YEV and there is no FAI flights.
> > > >
> > > > How do I adjust the formula to count the occurance of YEV and FAI taking
> > > > into account that from the 18th to 31st the T,Th now go to YEV and not FAI.
> > > >
> > > > Long winded, Sorry, but wanted to ensure I have all the pertinent
> > > > information for assistance.
> > > >
> > > > Thanks
> > > >