Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find a match for three variables to determine the result?
I have a table called Dates with data that looks like the following: A B C A 3 Spin Start date End date Month of Spin 4 10 1/1/09 0:00 1/31/09 23:59 1 5 10 2/1/09 0:00 2/28/09 23:59 2 6 10 3/1/09 0:00 3/31/09 23:59 3 7 10 4/1/09 0:00 4/30/09 23:59 4 8 10 5/1/09 0:00 5/31/09 23:59 5 9 10 6/1/09 0:00 6/30/09 23:59 6 10 11 3/1/09 0:00 3/31/09 23:59 1 11 11 4/1/09 0:00 4/30/09 23:59 2 12 11 5/1/09 0:00 5/31/09 23:59 3 13 11 6/1/09 0:00 6/30/09 23:59 4 14 11 7/1/09 0:00 7/31/09 23:59 5 15 11 8/1/09 0:00 8/31/09 23:59 6 16 12 5/1/09 0:00 5/31/09 23:59 1 17 12 6/1/09 0:00 6/30/09 23:59 2 18 12 7/1/09 0:00 7/31/09 23:59 3 19 12 8/1/09 0:00 8/31/09 23:59 4 20 12 9/1/09 0:00 9/30/09 23:59 5 21 12 10/1/09 0:00 10/31/09 23:59 6 I have another sheet (Actuals) that provides a Spin number (Row A Spin) and a Date the report was generated (Row B Date of report). A B C 3 Spin Date of Report Month of Spin 4 10 1/15/2009 5 11 6/3/2009 6 10 5/5/2009 7 11 7/17/2009 8 11 4/25/2009 9 12 1/15/2009 10 12 9/15/2009 11 11 4/21/2009 12 12 5/23/2009 13 11 8/9/2009 How do I find the Month of the spin associated with the Actuals data by validating the Spin number and date match on the Dates Spreadsheet?? Help!! Thanks much!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find a match for three variables to determine the result?
If each value pair only occurs once ( no repeats), you might try using
sumproduct; something like (aircode): [on sheet2!C2, where you want the value to show up for that row] =sumproduct((Sheet1!A:A= A2)*1, (Sheet2!B:B=B2)*1, (Sheet2!D:D)) so if the first two conditions evaluate to true, they get a value of one, which is multiplied against the 'month' value in column D to return just that value. However, it also appears that your original values are only the first of each month, with no hours/minutes, and the comparison values can be any day, along with hours/minutes. There are a few ways to do this, so I'll let you pick your preference. For me I'd probably extract the year/month and create a simplified date- something like: =DATE(YEAR(B2),MONTH(B2),1) so the final formula would be something like: =sumproduct((Sheet1!A:A= A2)*1, (Sheet2!B:B=(DATE(YEAR(B2),MONTH(B2),1)))*1, (Sheet2!D:D)) HTH, Keith "SykesvilleJim" wrote: I have a table called Dates with data that looks like the following: A B C A 3 Spin Start date End date Month of Spin 4 10 1/1/09 0:00 1/31/09 23:59 1 5 10 2/1/09 0:00 2/28/09 23:59 2 6 10 3/1/09 0:00 3/31/09 23:59 3 7 10 4/1/09 0:00 4/30/09 23:59 4 8 10 5/1/09 0:00 5/31/09 23:59 5 9 10 6/1/09 0:00 6/30/09 23:59 6 10 11 3/1/09 0:00 3/31/09 23:59 1 11 11 4/1/09 0:00 4/30/09 23:59 2 12 11 5/1/09 0:00 5/31/09 23:59 3 13 11 6/1/09 0:00 6/30/09 23:59 4 14 11 7/1/09 0:00 7/31/09 23:59 5 15 11 8/1/09 0:00 8/31/09 23:59 6 16 12 5/1/09 0:00 5/31/09 23:59 1 17 12 6/1/09 0:00 6/30/09 23:59 2 18 12 7/1/09 0:00 7/31/09 23:59 3 19 12 8/1/09 0:00 8/31/09 23:59 4 20 12 9/1/09 0:00 9/30/09 23:59 5 21 12 10/1/09 0:00 10/31/09 23:59 6 I have another sheet (Actuals) that provides a Spin number (Row A Spin) and a Date the report was generated (Row B Date of report). A B C 3 Spin Date of Report Month of Spin 4 10 1/15/2009 5 11 6/3/2009 6 10 5/5/2009 7 11 7/17/2009 8 11 4/25/2009 9 12 1/15/2009 10 12 9/15/2009 11 11 4/21/2009 12 12 5/23/2009 13 11 8/9/2009 How do I find the Month of the spin associated with the Actuals data by validating the Spin number and date match on the Dates Spreadsheet?? Help!! Thanks much!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find a match for three variables to determine the result?
Try this:
Dates table in the range Date!A4:D21 Enter this formula in Actual!C4: =SUMPRODUCT(--(Dates!A$4:A$21=A4),--(B4=Dates!B$4:B$21),--(B4<=Dates!C$4:C$21),Dates!D$4:D$21) Copy down as needed. -- Biff Microsoft Excel MVP "SykesvilleJim" wrote in message ... I have a table called "Dates" with data that looks like the following: A B C A 3 Spin Start date End date Month of Spin 4 10 1/1/09 0:00 1/31/09 23:59 1 5 10 2/1/09 0:00 2/28/09 23:59 2 6 10 3/1/09 0:00 3/31/09 23:59 3 7 10 4/1/09 0:00 4/30/09 23:59 4 8 10 5/1/09 0:00 5/31/09 23:59 5 9 10 6/1/09 0:00 6/30/09 23:59 6 10 11 3/1/09 0:00 3/31/09 23:59 1 11 11 4/1/09 0:00 4/30/09 23:59 2 12 11 5/1/09 0:00 5/31/09 23:59 3 13 11 6/1/09 0:00 6/30/09 23:59 4 14 11 7/1/09 0:00 7/31/09 23:59 5 15 11 8/1/09 0:00 8/31/09 23:59 6 16 12 5/1/09 0:00 5/31/09 23:59 1 17 12 6/1/09 0:00 6/30/09 23:59 2 18 12 7/1/09 0:00 7/31/09 23:59 3 19 12 8/1/09 0:00 8/31/09 23:59 4 20 12 9/1/09 0:00 9/30/09 23:59 5 21 12 10/1/09 0:00 10/31/09 23:59 6 I have another sheet (Actuals) that provides a Spin number (Row A - "Spin") and a Date the report was generated (Row B - "Date of report"). A B C 3 Spin Date of Report Month of Spin 4 10 1/15/2009 5 11 6/3/2009 6 10 5/5/2009 7 11 7/17/2009 8 11 4/25/2009 9 12 1/15/2009 10 12 9/15/2009 11 11 4/21/2009 12 12 5/23/2009 13 11 8/9/2009 How do I find the Month of the spin associated with the "Actuals" data by validating the Spin number and date match on the "Dates" Spreadsheet?? Help!! Thanks much!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
two sets of variables to find result | Excel Discussion (Misc queries) | |||
two variables to get result | Excel Worksheet Functions | |||
Find 2nd, 3rd, etc Result with index/match | Excel Worksheet Functions | |||
TWO VARIABLES - ONE RESULT | Excel Discussion (Misc queries) | |||
determine colouring of variables of a pie chart | Charts and Charting in Excel |