Thanks for that assistance Marcelo, I apparantly didn't give you all the
correct information, I posted another question relative to this one you
replied to on the forum this afternoon. I am going to copy it and forward it
to you since it is a continuation of this reply, hope you are able to assist.
Here is more information for my dillema.
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
"Marcelo" wrote:
> hi Yukon,
>
> try to use sumproduct as
> =sumproduct(--(a3:a23="name")*(b3:b23=({"Mon";"Wed";"Fri"})))
> as it is an array formula entre with Ctrl+Shif+enter not just enter
>
> adjust the range as you need, I use "name" as a people name
>
> hth
> regards from Brazil
> Marcelo
>
> "yukon_phil" escreveu:
>
> > I am trying to get a count of occurances. For example;
> >
> > I have a schedule for 20 people for a month and have "N" in varying days of
> > the week and I need to have 2 separate counts for the occurance for each
> > person. One for the days Mon,Wed,Fri. and another for Tue,Thur & Sun. Then I
> > want to multipy the returned count by a specific value. In the case of
> > Mon.,Wed., & Fri. I need to multipy by 1370, in the other case it is 1290.
> >
> > Any assistance would be greatly appreciated.
|
Similar Threads
