I have a Jet Report to list all of our company's different entities/tenants down the left side, and monthly dates across the top. When I run the report, all the rows appear with all of our different tenants and the entity to which they belong (some entities will have multiple tenants). Within the table, Jet looks up the tenant's monthly rent for the date at the top of that column and populates it. I have the spreadsheet build out to show 5 years (by month) or rent data for every tenant. It starts out as a single row in design mode, and when I run it, I'll end up with up to 75 rows (tenants).
Here's my issues/questions:
#1. I want to be able to make another table on another tab that will summarize the data in the table above by entity and year (meaning one cell will be able to tell you how much rent an entity expects to collect for an entire year). The problem with using SUMIFS is that it will only allow you to sum a single row or column of data, and in order to have the formula capture all the data that will appear when the report is ran, I need to include the row below the first row, so that it picks up the rest of the data when the table expands. I cant think of any formulas and functions that I can use to make this happen without doing 12 SUMIF formulas in one cell, which obviously is time consuming and a pain in the butt.
#2. Is there a Jet Function that would allow me to list a certain date of every month (1st of every month, etc) for a specified time range in the columns across the top without me having to manually enter them in?
I very much appreciate any assistance you can offer!