Sorry, I am not very good at excel so I hope I can explain what I want. I have a spreadsheet that calculates how long of a term my leases are. I would like to have a total of the sq ft of those expiring in less than a year, less than 2 years but longer than 1 year, less than 3 years but longer than 2 years, etc. I can't figure out how to do this with the tools wizard or the sum if function. thanks in advance for your time.
You can use sumif(s) but i can tell u for certain if i see some sample data.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Here is an example of the table.
ste sq name rent beg end term
1 302 tenant 1 646.36 Nov-07 Oct-12 5.0 numbers in term column are calcuated by +(F2-E2)/365
2 356 tenant 1 675.68 Nov-10 Oct-12 2.0
3 1625 tenant 1 2,776.34 Nov-11 Oct-12 1.0
4 982 tenant 1 2,148.30 Nov-08 Oct-12 4.0
5 1729 tenant 1 3,241.84 Nov-11 Oct-12 1.0
6 332 tenant 1 608.58 Nov-11 Oct-12 1.0
7 328 tenant 1 629.00 May-11 Oct-12 1.5
8 1198 tenant 1 2,260.05 Nov-10 Oct-12 2.0
9 1715 tenant 1 3,215.38 Nov-11 Oct-12 1.0
10 1017 tenant 1 646.36 Jan-12 Oct-12 0.8
11 1053 tenant 1 675.68 Nov-10 Oct-12 2.0
12 1089 tenant 1 2,776.34 Nov-11 Oct-12 1.0
13 1124 tenant 1 2,148.30 Feb-12 Oct-12 0.7
14 1160 tenant 1 3,241.84 Nov-11 Oct-12 1.0
15 1196 tenant 1 608.58 Nov-11 Oct-12 1.0
16 1232 tenant 1 629.00 Nov-11 Oct-12 1.0
Want to get totals of column b (sq) if last column is
less than 1 yr but more than 0.0 (so vacant sq does not get in calculation)
at least 1 yr but less than 2 yr
at least 2 yr but less than 3 yr
at least 3 yr but less than 5 yr
5 or more
Would it be possible for you to attach the file directly instead of copying the numbers here? Its a huge waste of time for us to then duplicate the figures to a excel file. Thanks much.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Sorry i did not know I could attach the file. How do I do that?
To Attach a File:
1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
See if this helps
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
Okay, here is the file. Thank you!!!
Unfortunately, I can't change the format for the spreadsheet for the terms as the president doesn't want it any other way.Originally Posted by Marcol
The reply I posted was before I saw your actual workbook, or indeed any workbook.
Can't you adapt the method to suit your situation, or .... are you a man or a mouse?
Squeek up ........ the president doesn't want it any other way.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks