INDEX MATCH formula

[Txlonghorn76]

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

 

[Mike H]

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:E29))

Mike

 

[Mike H]

Just for the exercise I think I fathomed out your named ranges

=SUMPRODUCT((MONTH(Dates)=MONTH(Thisday))*(Names=Tgt)*(Tbl))

Thisday must contain a date of the month you are searching for

Mike

 

[Txlonghorn76]

Hi,

I am getting a #Value! Error using this formula:
=SUMPRODUCT((MONTH(A2:A8)=9)*(A1:E1="CCC")*(A1:E8))

 

[Pete_UK]

Your final range A1:E8 does not cover the same rows as the first
(A2:A8)

Hope this helps.

Pete

 

[Txlonghorn76]

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]

I think you would need it to be:

=SUMPRODUCT((MONTH(A2:A8)=9)*(B1:E1="CCC")*(B2:E8))

Hope this helps.

Pete

 

[Txlonghorn76]

This worked great! Thanks!

I think you would need it to be:

=SUMPRODUCT((MONTH(A2:A8)=9)*(B1:E1="CCC")*(B2:E8))

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete