Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get a value returned when I require 2 criteria to be met
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get a value returned when I require 2 criteria to be met
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get a value returned when I require 2 criteria to be
Fantastic, thank you very much for your assistance.
"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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get a value returned when I require 2 criteria to be
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multple criteria dilemma | Excel Worksheet Functions | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
Returning Results Based on Two Criteria | Excel Worksheet Functions | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |