| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Assistance Please
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Assistance Please
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Assistance Please
.... 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";"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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Assistance Please
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Assistance Please
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";"T h";"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<atjohntopley.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";"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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Assistance Please
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";"T h";"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<atjohntopley.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";"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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Assistance Please
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";"T h";"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<atjohntopley.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";"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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Assistance Please
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";"T h";"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<atjohntopley.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";"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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Assistance Please
I just sent the sheet to the following address, I hope I got it correct:
) My address is ) 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";"T h";"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<atjohntopley.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";"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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is it possible? | Excel Worksheet Functions | |||
| formula assistance - percentage difference | Excel Worksheet Functions | |||
| Formula Assistance ( | Excel Worksheet Functions | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| I need assistance with wrting an Excel formula | Excel Worksheet Functions | |||
Linear Mode
