Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Both Vertical and Horizontal Conditons
I'm trying to figure out a formula the would match a month on the verital
column and then three sets of critera in the horizontal column such as year and two other words (BGT & TRN). So for instance is I want to match the date and year from A5 & B5 plus the words BGT & TRN in and return the value D5 from the above data and would return 100. A B C D 2009 2008 BGT BGT TRN TRN 1 JAN 100 301 2 FEB 101 401 3 MAR 201 501 5 JAN 2009 * (Match the date from A5 & B5 with Value in C5) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Both Vertical and Horizontal Conditons
Ok, we'll need some clarifications...
Is this what your table looks like: ...........A..........B..........C 1.................2009.....2008 2.................BGT......BGT 3.................TRN......TRN 4......JAN....100........301 5......FEB....101........401 6......MAR...201.......501 5 JAN 2009 Is that in 2 cells? 5 Jan 2009 Is 5 Jan a true Excel date? Are the months in your table in a calender sequence Jan, Feb, Mar, Apr, May, Jun, Jul, ... Dec If BGT TRN is entered in every column then all you need to do is match the year number. -- Biff Microsoft Excel MVP "GaryS" wrote in message ... I'm trying to figure out a formula the would match a month on the verital column and then three sets of critera in the horizontal column such as year and two other words (BGT & TRN). So for instance is I want to match the date and year from A5 & B5 plus the words BGT & TRN in and return the value D5 from the above data and would return 100. A B C D 2009 2008 BGT BGT TRN TRN 1 JAN 100 301 2 FEB 101 401 3 MAR 201 501 5 JAN 2009 * (Match the date from A5 & B5 with Value in C5) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Both Vertical and Horizontal Conditons
Here's a better picture of the cells.... thanks for the reply. To answer your
question yes I'm using excel dates... the 5 was supposed to represent row 5 with a Month and Year in two different cells. There are lots of other cells feeding the monthly totals below. And yes the months do go in Calendar sequence, however it's dynamic report that the first month changes based on the current month looking twelve months forward. Also I'd have to match Year, BGT, and TRN because I have three sets of columns: For 2007,2008,2009 then in each year I have Budget, Forecast and Actual. Finally in each of them I have three segments called Transient, Group, and Contract. I hope this helps and thank you again for your help, much appreciated. ..............A.................B..............C.. ................D..............E 1............................2009.........2008.... .......2009..........2008 2............................BGT...........BGT.... .........FCT..........FCT 3...........................TRN............GRP.... ........TRN.........GRP 4...........JAN...........101.............301..... ........750.........111 5...........FEB...........101.............401..... ........850.........222 6...........MAR..........201.............501...... .......950.........333 7................................................. ........................................ 8.............................................TRN. ..............FCT 9..........JAN.............2008...........*....... ............* ..............(*Return Values) "T. Valko" wrote: Ok, we'll need some clarifications... Is this what your table looks like: ...........A..........B..........C 1.................2009.....2008 2.................BGT......BGT 3.................TRN......TRN 4......JAN....100........301 5......FEB....101........401 6......MAR...201.......501 5 JAN 2009 Is that in 2 cells? 5 Jan 2009 Is 5 Jan a true Excel date? Are the months in your table in a calender sequence Jan, Feb, Mar, Apr, May, Jun, Jul, ... Dec If BGT TRN is entered in every column then all you need to do is match the year number. -- Biff Microsoft Excel MVP "GaryS" wrote in message ... I'm trying to figure out a formula the would match a month on the verital column and then three sets of critera in the horizontal column such as year and two other words (BGT & TRN). So for instance is I want to match the date and year from A5 & B5 plus the words BGT & TRN in and return the value D5 from the above data and would return 100. A B C D 2009 2008 BGT BGT TRN TRN 1 JAN 100 301 2 FEB 101 401 3 MAR 201 501 5 JAN 2009 * (Match the date from A5 & B5 with Value in C5) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Both Vertical and Horizontal Conditons
Hi,
Try this SUMPRODUCT(($B$5:$B$7=$B14)*($C$2:$F$2=$C14)*(($C$ 3:$F$3=D$13)+($C$4:$F$4=D$13)),$C$5:$F$7) I have assumed that the data is in range B2:F7. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "GaryS" wrote in message ... Here's a better picture of the cells.... thanks for the reply. To answer your question yes I'm using excel dates... the 5 was supposed to represent row 5 with a Month and Year in two different cells. There are lots of other cells feeding the monthly totals below. And yes the months do go in Calendar sequence, however it's dynamic report that the first month changes based on the current month looking twelve months forward. Also I'd have to match Year, BGT, and TRN because I have three sets of columns: For 2007,2008,2009 then in each year I have Budget, Forecast and Actual. Finally in each of them I have three segments called Transient, Group, and Contract. I hope this helps and thank you again for your help, much appreciated. .............A.................B..............C... ...............D..............E 1............................2009.........2008.... .......2009..........2008 2............................BGT...........BGT.... .........FCT..........FCT 3...........................TRN............GRP.... ........TRN.........GRP 4...........JAN...........101.............301..... ........750.........111 5...........FEB...........101.............401..... ........850.........222 6...........MAR..........201.............501...... .......950.........333 7................................................. ........................................ 8.............................................TRN. ..............FCT 9..........JAN.............2008...........*....... ............* .............(*Return Values) "T. Valko" wrote: Ok, we'll need some clarifications... Is this what your table looks like: ...........A..........B..........C 1.................2009.....2008 2.................BGT......BGT 3.................TRN......TRN 4......JAN....100........301 5......FEB....101........401 6......MAR...201.......501 5 JAN 2009 Is that in 2 cells? 5 Jan 2009 Is 5 Jan a true Excel date? Are the months in your table in a calender sequence Jan, Feb, Mar, Apr, May, Jun, Jul, ... Dec If BGT TRN is entered in every column then all you need to do is match the year number. -- Biff Microsoft Excel MVP "GaryS" wrote in message ... I'm trying to figure out a formula the would match a month on the verital column and then three sets of critera in the horizontal column such as year and two other words (BGT & TRN). So for instance is I want to match the date and year from A5 & B5 plus the words BGT & TRN in and return the value D5 from the above data and would return 100. A B C D 2009 2008 BGT BGT TRN TRN 1 JAN 100 301 2 FEB 101 401 3 MAR 201 501 5 JAN 2009 * (Match the date from A5 & B5 with Value in C5) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Both Vertical and Horizontal Conditons
OK, I'm still confused!
Based on the updated table there is no entry for JAN 2008 TRN. For JAN 2008 FCT I guess the result should be 111. ????? -- Biff Microsoft Excel MVP "GaryS" wrote in message ... Here's a better picture of the cells.... thanks for the reply. To answer your question yes I'm using excel dates... the 5 was supposed to represent row 5 with a Month and Year in two different cells. There are lots of other cells feeding the monthly totals below. And yes the months do go in Calendar sequence, however it's dynamic report that the first month changes based on the current month looking twelve months forward. Also I'd have to match Year, BGT, and TRN because I have three sets of columns: For 2007,2008,2009 then in each year I have Budget, Forecast and Actual. Finally in each of them I have three segments called Transient, Group, and Contract. I hope this helps and thank you again for your help, much appreciated. .............A.................B..............C... ...............D..............E 1............................2009.........2008.... .......2009..........2008 2............................BGT...........BGT.... .........FCT..........FCT 3...........................TRN............GRP.... ........TRN.........GRP 4...........JAN...........101.............301..... ........750.........111 5...........FEB...........101.............401..... ........850.........222 6...........MAR..........201.............501...... .......950.........333 7................................................. ........................................ 8.............................................TRN. ..............FCT 9..........JAN.............2008...........*....... ............* .............(*Return Values) "T. Valko" wrote: Ok, we'll need some clarifications... Is this what your table looks like: ...........A..........B..........C 1.................2009.....2008 2.................BGT......BGT 3.................TRN......TRN 4......JAN....100........301 5......FEB....101........401 6......MAR...201.......501 5 JAN 2009 Is that in 2 cells? 5 Jan 2009 Is 5 Jan a true Excel date? Are the months in your table in a calender sequence Jan, Feb, Mar, Apr, May, Jun, Jul, ... Dec If BGT TRN is entered in every column then all you need to do is match the year number. -- Biff Microsoft Excel MVP "GaryS" wrote in message ... I'm trying to figure out a formula the would match a month on the verital column and then three sets of critera in the horizontal column such as year and two other words (BGT & TRN). So for instance is I want to match the date and year from A5 & B5 plus the words BGT & TRN in and return the value D5 from the above data and would return 100. A B C D 2009 2008 BGT BGT TRN TRN 1 JAN 100 301 2 FEB 101 401 3 MAR 201 501 5 JAN 2009 * (Match the date from A5 & B5 with Value in C5) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Horizontal and Vertical Charts | Charts and Charting in Excel | |||
Freeze Vertical and Horizontal | Excel Discussion (Misc queries) | |||
I want horizontal and vertical lines | Excel Worksheet Functions | |||
Vertical to Horizontal | Excel Discussion (Misc queries) | |||
Vertical to horizontal | Excel Discussion (Misc queries) |