Hi, I'm playing with a table, I'd like to sum values of rows with the 2 most recent dates..where I don't know which is the most recent (table will expand downwards).
I've tried with INDIRECT in Sheet2 of attached file, but it fails. Can you please help me?
matrix.jpg
Thank you!
If you turn your table into a real Excel table, then you won't need to know the final row number.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
uhm..not very expert of this. How would my formula change, then?Originally Posted by AliGW
And anyway, I know this might look overcomplicated to you considering the table trick, how could I do it without? I.e. the way I was trying?
This is the easy way. Try it.
1. Select the range B2 to G15.
2. Insert ribbon - click Table.
3. Change formula in R7 to this:
=SUMPRODUCT((Table1[Date]<(LOOKUP(2,1/(Table1[Date]<>""),Table1[Date])+1))*(Table1[Date]>=(LARGE(Table1[Date],COUNTIF(Table1[Date],MAX(Table1[Date]))+1)))*(Table1[[A]:[E]]))
4. Now add another row with 1104 in the date column and a value somewhere on the row. The formula will adjust its output correctly.
Last edited by AliGW; 11-06-2017 at 09:29 AM.
Let us know how you get on.
AliGW, that's very useful, thank you!
However, if possible I'd like to solve this using brute force also :P and avoiding the table..then I will call it solved ^^
Up to you, of course. I'm sure someone will help. As a matter of interest, what is your reason for wanting to avoid the table? In what way does it or would it cause you problems, other than being new to you?
First off, I would HIGHLY recommend that you change your numbers in the Date column into actual dates that Excel recognizes as dates.
That is, change 1030 to 10/30/2017, 1102 to 11/02/2017, etc.
Then, you can format these dates however you'd like.
I agree with Ali that you should make your range into a table if you plan on adding rows to it. If you do not want to do this due to inexperience, I suggest familiarizing yourself with tables rather than looking for another solution. Another solution would be implementing dynamic named ranges (which is more complex than using tables so we won't go there).
That being said, here is a solution that uses static ranges. You will have to make sure that your data will never exceed the ranges that you put in your formulas.
=SUMPRODUCT((B3:B100>=LARGE(B3:B100,COUNTIF(B3:B100,MAX(B3:B100))+1))*(C3:G100))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks