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

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

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

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

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 -

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 -

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 -

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 -

INDEX MATCH formula
 

You're welcome - thanks for feeding back.

Pete

On Sep 17, 1:57*pm, Txlonghorn76
wrote:
This worked great! Thanks!