Formula Assistance Please

yukon_phil · 07-18-2006, 03:45 PM

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

Toppers · 07-18-2006, 05:05 PM

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
>

Toppers · 07-18-2006, 05:20 PM

.... remove "=" from second SUMPRODUCT addition!

=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"})))

Sorry!

"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
> >

yukon_phil · 07-18-2006, 05:20 PM

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
> >

Toppers · 07-19-2006, 01:10 AM

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
> > >

Toppers · 07-19-2006, 01:55 AM

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
> > > >

yukon_phil · 07-19-2006, 10:40 AM

Thanks for this, I just tried this option and I do like this one as we know
the dates of the change will be different each year. I will email the
workbook to you to have a look at it and make any comments or suggestions.

Thanks

"Toppers" wrote:

> 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
> > > > >

yukon_phil · 07-19-2006, 10:40 AM

There seems to be some error in the forumula, I have looked through it but I
don't see it, I am still learning these more complex formula so if you could
troubleshoot that would be appreiciated. I will send the sheet to you know.

"Toppers" wrote:

> 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
> > > > >

yukon_phil · 07-19-2006, 11:00 AM

I just sent the sheet to the following address, I hope I got it correct:

(toppers@johntopley.fsnet.co.uk)

My address is (ndfcpres@hotmail.com)

Let me know if I have it correct.

Thanks

"Toppers" wrote:

> 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
> > > > >