| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX MATCH formula
Last week I was I had asked for a formula that would give me the yesterday's
numbers for an employee that pulled to a Summary Sheet from a table in another worksheet. I was given this awesome formula: =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) I would like to tweak this formula just a bit so that it pulls the running sum for the month instead of just 1 day. Summary sheet: 09/10/2008 Name Yesterday's numbers MTD Numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX MATCH formula
Hi,
I'm afraid that trying to work out you named ranges was a bit too much so if you want this as named ranges then you'll have to convert it. I would actually used cell references for the month (10) and the name CCC but this should do what you require. =SUMPRODUCT((MONTH(A2:A29)=10)*(B1:E1="CCC")*(B2:E 29)) Mike "Txlonghorn76" wrote: Last week I was I had asked for a formula that would give me the yesterday's numbers for an employee that pulled to a Summary Sheet from a table in another worksheet. I was given this awesome formula: =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) I would like to tweak this formula just a bit so that it pulls the running sum for the month instead of just 1 day. Summary sheet: 09/10/2008 Name Yesterday's numbers MTD Numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX MATCH formula
Just for the exercise I think I fathomed out your named ranges
=SUMPRODUCT((MONTH(Dates)=MONTH(Thisday))*(Names=T gt)*(Tbl)) Thisday must contain a date of the month you are searching for Mike "Mike H" wrote: Hi, I'm afraid that trying to work out you named ranges was a bit too much so if you want this as named ranges then you'll have to convert it. I would actually used cell references for the month (10) and the name CCC but this should do what you require. =SUMPRODUCT((MONTH(A2:A29)=10)*(B1:E1="CCC")*(B2:E 29)) Mike "Txlonghorn76" wrote: Last week I was I had asked for a formula that would give me the yesterday's numbers for an employee that pulled to a Summary Sheet from a table in another worksheet. I was given this awesome formula: =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) I would like to tweak this formula just a bit so that it pulls the running sum for the month instead of just 1 day. Summary sheet: 09/10/2008 Name Yesterday's numbers MTD Numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX MATCH formula
Hi,
I am getting a #Value! Error using this formula: =SUMPRODUCT((MONTH(A2:A8)=9)*(A1:E1="CCC")*(A1:E8) ) "Mike H" wrote: Hi, I'm afraid that trying to work out you named ranges was a bit too much so if you want this as named ranges then you'll have to convert it. I would actually used cell references for the month (10) and the name CCC but this should do what you require. =SUMPRODUCT((MONTH(A2:A29)=10)*(B1:E1="CCC")*(B2:E 29)) Mike "Txlonghorn76" wrote: Last week I was I had asked for a formula that would give me the yesterday's numbers for an employee that pulled to a Summary Sheet from a table in another worksheet. I was given this awesome formula: =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) I would like to tweak this formula just a bit so that it pulls the running sum for the month instead of just 1 day. Summary sheet: 09/10/2008 Name Yesterday's numbers MTD Numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX MATCH formula
Your final range A1:E8 does not cover the same rows as the first
(A2:A8) Hope this helps. Pete On Sep 16, 5:41*pm, Txlonghorn76 wrote: Hi, I am getting a #Value! Error using this formula: =SUMPRODUCT((MONTH(A2:A8)=9)*(A1:E1="CCC")*(A1:E8) ) "Mike H" wrote: Hi, I'm afraid that trying to work out you named ranges was a bit too much so if you want this as named ranges then you'll have to convert it. I would actually used cell references for the month (10) and the name CCC but this should do what you require. =SUMPRODUCT((MONTH(A2:A29)=10)*(B1:E1="CCC")*(B2:E 29)) Mike "Txlonghorn76" wrote: Last week I was I had asked for a formula that would give me the yesterday's numbers for an employee that pulled to a Summary Sheet from a table in another worksheet. I was given this awesome formula: =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) I would like to tweak this formula just a bit so that it pulls the running sum for the month instead of just 1 day. Summary sheet: 09/10/2008 * * Name *Yesterday's numbers * *MTD Numbers AAA * BBB CCC * DDD * Table to pull data from: * * * * * * * * * * * * * * * *AAA * * * * *BBB * * * * * CCC * * * *DDD 09/09/2008 * *30 * * *31 * * *32 * * *33 09/10/2008 * *34 * * *35 * * *36 * * *37 09/11/2008 * *38 * * *39 * * *40 * * *41 09/12/2008 * *42 * * *43 * * *44 * * *45 09/13/2008 * *46 * * *47 * * *48 * * *49 09/14/2008 * *50 * * *51 * * *52 * * *53- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX MATCH formula
I changed the formula to:
=SUMPRODUCT((MONTH(A2:A8)=9)*(A1:E1="CCC")*(A2:A8) ) now I am getting 277914 when I'm looking for the sum which is 280. "Pete_UK" wrote: Your final range A1:E8 does not cover the same rows as the first (A2:A8) Hope this helps. Pete On Sep 16, 5:41 pm, Txlonghorn76 wrote: Hi, I am getting a #Value! Error using this formula: =SUMPRODUCT((MONTH(A2:A8)=9)*(A1:E1="CCC")*(A1:E8) ) "Mike H" wrote: Hi, I'm afraid that trying to work out you named ranges was a bit too much so if you want this as named ranges then you'll have to convert it. I would actually used cell references for the month (10) and the name CCC but this should do what you require. =SUMPRODUCT((MONTH(A2:A29)=10)*(B1:E1="CCC")*(B2:E 29)) Mike "Txlonghorn76" wrote: Last week I was I had asked for a formula that would give me the yesterday's numbers for an employee that pulled to a Summary Sheet from a table in another worksheet. I was given this awesome formula: =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) I would like to tweak this formula just a bit so that it pulls the running sum for the month instead of just 1 day. Summary sheet: 09/10/2008 Name Yesterday's numbers MTD Numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX MATCH formula
I think you would need it to be:
=SUMPRODUCT((MONTH(A2:A8)=9)*(B1:E1="CCC")*(B2:E8) ) Hope this helps. Pete On Sep 16, 7:11*pm, Txlonghorn76 wrote: I *changed the formula to: =SUMPRODUCT((MONTH(A2:A8)=9)*(A1:E1="CCC")*(A2:A8) ) now I am getting 277914 when I'm looking for the sum which is 280. "Pete_UK" wrote: Your final range A1:E8 does not cover the same rows as the first (A2:A8) Hope this helps. Pete On Sep 16, 5:41 pm, Txlonghorn76 wrote: Hi, I am getting a #Value! Error using this formula: =SUMPRODUCT((MONTH(A2:A8)=9)*(A1:E1="CCC")*(A1:E8) ) "Mike H" wrote: Hi, I'm afraid that trying to work out you named ranges was a bit too much so if you want this as named ranges then you'll have to convert it. I would actually used cell references for the month (10) and the name CCC but this should do what you require. =SUMPRODUCT((MONTH(A2:A29)=10)*(B1:E1="CCC")*(B2:E 29)) Mike "Txlonghorn76" wrote: Last week I was I had asked for a formula that would give me the yesterday's numbers for an employee that pulled to a Summary Sheet from a table in another worksheet. I was given this awesome formula: =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) I would like to tweak this formula just a bit so that it pulls the running sum for the month instead of just 1 day. Summary sheet: 09/10/2008 * * Name *Yesterday's numbers * *MTD Numbers AAA * BBB CCC * DDD * Table to pull data from: * * * * * * * * * * * * * * * *AAA * * * * *BBB * * * * * CCC * * * *DDD 09/09/2008 * *30 * * *31 * * *32 * * *33 09/10/2008 * *34 * * *35 * * *36 * * *37 09/11/2008 * *38 * * *39 * * *40 * * *41 09/12/2008 * *42 * * *43 * * *44 * * *45 09/13/2008 * *46 * * *47 * * *48 * * *49 09/14/2008 * *50 * * *51 * * *52 * * *53- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX MATCH formula
This worked great! Thanks!
"Pete_UK" wrote: I think you would need it to be: =SUMPRODUCT((MONTH(A2:A8)=9)*(B1:E1="CCC")*(B2:E8) ) Hope this helps. Pete On Sep 16, 7:11 pm, Txlonghorn76 wrote: I changed the formula to: =SUMPRODUCT((MONTH(A2:A8)=9)*(A1:E1="CCC")*(A2:A8) ) now I am getting 277914 when I'm looking for the sum which is 280. "Pete_UK" wrote: Your final range A1:E8 does not cover the same rows as the first (A2:A8) Hope this helps. Pete On Sep 16, 5:41 pm, Txlonghorn76 wrote: Hi, I am getting a #Value! Error using this formula: =SUMPRODUCT((MONTH(A2:A8)=9)*(A1:E1="CCC")*(A1:E8) ) "Mike H" wrote: Hi, I'm afraid that trying to work out you named ranges was a bit too much so if you want this as named ranges then you'll have to convert it. I would actually used cell references for the month (10) and the name CCC but this should do what you require. =SUMPRODUCT((MONTH(A2:A29)=10)*(B1:E1="CCC")*(B2:E 29)) Mike "Txlonghorn76" wrote: Last week I was I had asked for a formula that would give me the yesterday's numbers for an employee that pulled to a Summary Sheet from a table in another worksheet. I was given this awesome formula: =index(tbl,match(Thisday-1,Dates,0),match(Tgt,Names,0)) I would like to tweak this formula just a bit so that it pulls the running sum for the month instead of just 1 day. Summary sheet: 09/10/2008 Name Yesterday's numbers MTD Numbers AAA BBB CCC DDD Table to pull data from: AAA BBB CCC DDD 09/09/2008 30 31 32 33 09/10/2008 34 35 36 37 09/11/2008 38 39 40 41 09/12/2008 42 43 44 45 09/13/2008 46 47 48 49 09/14/2008 50 51 52 53- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX MATCH formula
You're welcome - thanks for feeding back.
Pete On Sep 17, 1:57*pm, Txlonghorn76 wrote: This worked great! Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula using INDEX and MATCH | Excel Worksheet Functions | |||
| INDEX MATCH formula | Excel Worksheet Functions | |||
| INDEX and MATCH in one formula... | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| index match formula | Excel Worksheet Functions | |||
Linear Mode
