Sum total per month per category of all contracts (sumproduct, table, dates)

[exc4libur]

Hello,
How are you guys?

I'm with a bit of a problem and for a while now I can't find a solution.
Its very hard for me to explain it in writing, I might confuse myself and others (lol).
So, I've attached a worksheet that explains exactly what I want to accomplish.

Thank you in advance!
ex4.

 

[Luke M]

Interesting challenge. Since the rate appears to be a NumYears * SQM * BaseRate, I believe this formula in cell E34 works nicely:
=SUMPRODUCT((Table2[Category]=E$33)*(Table2[Rented]<$A34)*(Table2[Expiry]>$A34)*(Table2[SQM])*(Table2[1])*(YEAR($A34)-YEAR(Table2[Rented])+1))

Checks if

1. Property is Residdential/Commercial
2. Has property been rented
3. is property still rented
4. Calculates rate (how many years * SQM * Rate

Since this uses SUMPRODUCT, things could start getting a little slow if you have a bunch of rows in your Table2, but should be good for awhile.

 

[Somendra Misra]

Hi,

Got bit confused looking at your file.

Just try below formula in E34 and copy right & down:

=SUMPRODUCT((E$33=Table2[Category])*($A34>=$E$13:$E$18)*($A34<=$F$13:$F$18)*(LEFT($L$12:$P$12,1)+0=$B34)*($L$13:$P$18))

Can't be sure whether it will give you right result or not?

Regards,

 

[exc4libur]

Hi guys, thanks for replying! I tested both and they didn't work.

Somendra, Hi, what was confusing? I can try and explain better!

Please find attached the file with both solutions from Luke and Somendra.

Rgds

 

[Somendra Misra]

@exc4libur

See the attached pic, this is what I saw when open your file in Excel 2010.



So all those error caused 1st confusion.

Then Project Year in column B is for which project?

Regards,

 

[exc4libur]

Ohhhh, I see somendra. Check this version, I saved it in "*.xls".

Project Year is the year based to determine the "$ per sqm" of the contract.

For instance;
For any 'new' contract rented (signed) on the 3/31/2016, the monthly rent due $ per sqm during the first 12 months (contract year 1) would be based on Project Year [2] = all dates between "4/30/2015 - 4/29/2016".

Please tell me if it works

 

[Somendra Misra]

@exc4libur

Again getting error. Can you translate the bold portion of the function. Is this IFERROR function?

=_xlfn.IFNA(VLOOKUP(K$32,Sheet1!$A$12:$P$18,MATCH(K34,Sheet1!$A$12:$P$12,0),),)

Regards,

 

[Haseeb A]

SM,

Yes. IFNA is introduced in XL2013, if you open file in pre 2013 versions will show like _xlfn.IFNA.

It is same like IFERROR(VLOOKUP(),)

 

[exc4libur]

Hey, yea I figured that out. Just substituted some formulas in the worksheet it must be working now Somendra.

I've attached the file.

Rgds

 

[Luke M]

I tested both and they didn't work.

Can you elaborate on what this means? It's hard to tell what "didn't work". Formula gave error, gave incorrect results, worksheet became corrupted, workbook evolved into a bowl of spaghetti...

 

[exc4libur]

Hey Luke,

Lol
Formulas gave incorrect results.

Rgds

 

[Luke M]

Bazinga! I think I got it. It didn't work because it gave the wrong value, which was because my guess about how to calculate rates was wrong. Initial rent changes based upon starting year. Tracing back to the top, I think I figured out that I need to add to initial rate 2 or 4 times the number of years, not multiply. Using structural references in first file, overall formula becomes:
=SUMPRODUCT((Table2[Category]=E$33)*(Table2[Rented]<$A34)*(Table2[Expiry]>$A34)*
(Table2[SQM])*((Table2[1])+(ROUNDUP(((YEAR($A34)-YEAR(Table2[Rented]))*12+1+MONTH($A34)-MONTH(Table2[Rented]))/12,0)-1)
*IF(E$33=$A$4,$B$4,$B$5)))

 

[exc4libur]

=SUMPRODUCT((Table2[Category]=E$33)*(Table2[Rented]<$A34)*(Table2[Expiry]>$A34)*(Table2[SQM])*((Table2[1])+(ROUNDUP(((YEAR($A34)-YEAR(Table2[Rented]))*12+1+MONTH($A34)-MONTH(Table2[Rented]))/12,0)-1)*IF(E$33=$A$4,$B$4,$B$5)))

Hey Luke,

It didn't work, still giving incorrect results.

I attached another worksheet less confusing and more friendly.
We can do this!!!!

Rgds

 

[NARAYANK991]

Hi ,

I am sure Luke will get back to you with a solution ; I would like to suggest that the next time you create a worksheet , please ensure that raw data is in separate sheets ( assuming your raw data needs more than one sheet ) , and calculations are in their own sheets.

Going through a mass of data which has your own formulae , which I do not know are required or not , only makes the task of troubleshooting so much more difficult.

A clean looking worksheet , which makes it very clear which cells / columns have input data , which have intermediate results , and where the final output should appear will make the system more maintainable in the long run , even if it is only for yourself.

Secondly , unless an Excel worksheet is serving as a Dashboard , turning off the row and column labels is somewhat surprising ; any formula needs to be written using row and column references ; having both labels turned off makes it more difficult than it should be.

Narayan

 

[Somendra Misra]

@exc4libur ,

See the attached file, column E & F. Just check for all condition. It uses your table 1 and Table 2 data till Column F.

Regards,

 

[exc4libur]

Hey Narayan!

Thank you for that, I will take it in to consideration.
I saw many examples of entering raw data in different worksheets and turning off row/col. labels is a habit of mine, because my laptop screen is very small

Anyways, I appreciate your attention and remarks!

Rgds
Exc4.

 

[Somendra Misra]

@exc4libur

Updated file.

Regards,

 

[exc4libur]

@exc4libur

Updated file.

Regards,

Somendra,

Thank you for the solution!

And thank you all for helping!