Formula Assistance Please

G

Guest

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
 
G

Guest

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
 
G

Guest

.... 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!
 
G

Guest

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

Guest

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 said:
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 said:
The fields for the dates are B1:AF31 and the days are B2:AF2, does this help?
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
The fields for the dates are B1:AF31 and the days are B2:AF2, does this help?

:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
The fields for the dates are B1:AF31 and the days are B2:AF2, does this help?

:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

([email protected])

My address is ([email protected])

Let me know if I have it correct.

Thanks

Toppers said:
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 said:
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 said:
The fields for the dates are B1:AF31 and the days are B2:AF2, does this help?

:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding up total hours worked on employee schedule using various criteria 0
Scrabble Value calculation for Welsh words 0
Is there an easy way to count when adjacent cells contain same data? 1
Is this even possible?? 1
SUMPRODUCT: dividing the formula of one cell by another. 1
Formula assistance please . . 3
Counting text problem - formula works initially 3
Recalculating schedule 1

Top