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";"T h";"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