• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Match

P

PaulFogel123

Member
Hello,

In the attached file, I have a simple database and underneath it a summary of that database. The summary table uses SUMPRODUCT formulas that work very well. The issue is that the summary table and the source database will have constantly shifting dates, and they won't be in the same order each month with new data. Right now, the formulas use specific ranges, but I need these ranges to be flexible depending upon the date specified in the summary table.

I've tried INDEX and MATCH, but these only pick up specific values (so far as I know), not ranges for use with SUMPRODUCT. I seem to recall there is a simple solution for this problem. Any ideas?

Thank you, Paul
 

Attachments

  • Test File.xlsx
    73.8 KB · Views: 8
Khalid,
Your formula is great. I wonder if I could ask for another favor. There is a variation on this formula to aggregate the detail. It too uses specific date ranges. I tried to follow the logic of what you did to apply to this slightly different formula and I'm having trouble. Can you look at it please?
Paul
 

Attachments

  • Test File 2.xlsx
    74.5 KB · Views: 4
Perfect! Thanks Narayan.
A question for my own education: Why are there two commas in the formula?

=SUMPRODUCT((DRG_Weight)*(MDC=$B29)*(INDEX($C$4:$K$20,,MATCH(D$25,$C$3:$K$3,0))))/SUMIF(MDC,$B29,INDEX($C$4:$K$20,,MATCH(D$25,$C$3:$K$3,0)))
 
Hi Paul ,

The INDEX function takes either two parameters or three , depending on whether the first parameter is a single column / row or multiple columns / rows.

For example , you can have :

=INDEX($C$4:$C$20 , 7)

where the 7 will refer to the seventh element of the single column range C4:C20 ; the result of the above formula will be the contents of cell C10.

However , if the first parameter is a multiple row , multiple column range , then we need to specify the row index as well as the column index.

For example , if you use :

=INDEX($C$4:$K$20 , 7)

Excel will generate a #REF! error , since the 7 refers to the seventh row , but with 9 columns in the range , Excel does not know which column is to be used for the output.

Using :

=INDEX($C$4:$K$20 , 7 , 4)

will tell Excel to retrieve the element which is at the intersection of the seventh row and fourth column within the range ; the result of the above formula will be the contents of cell F10.

Now , if we use the row index of 0 , Excel interprets it to mean all the rows ; similarly , a column index of 0 means all the columns.

Thus , if we use a formula such as :

=INDEX($C$4:$K$20 , 0 , 4)

Excel will return an array of values in the fourth column i.e. the range of values F4:F20.

Similarly , a formula such as :

=INDEX($C$4:$K$20 , 7 , 0)

will return a horizontal array of values from the seventh row C10:K10.

Using a comma without any number after it , tells Excel to use the value 0 for that parameter ; thus , the following two are equivalent :

=INDEX($C$4:$K$20 , 0 , 4)
=INDEX($C$4:$K$20 ,, 4)

So also are the following two :

=INDEX($C$4:$K$20 , 7 , 0)
=INDEX($C$4:$K$20 , 7 ,)

Narayan
 
You must log in or register to reply here.
Back
Top