Sum data based on Row and Column variables (Excel 2007)

[RBenny]

Hello all,
Glad to be back on posting.

I'm working on a workbook that will combine a cash flow report by category and also by date. The categories can change (which I think is the easiest part) but so also can the frequency of data. For instance, you choose how you want to see the data, monthly, quarterly, annually and this will change the Row that the data should be summarized based off of.

Examples:
1) Annual: Sheet 1: Row = Rental Revenue and Column = 2001. I want to pull from sheet 2 all of the Rows that are mapped to Rental Revenue and all of the Columns that are mapped to 2001.
2) Quarterly: same idea but by quarter.
3) Monthly: No issue, just a SUMIF.

In Sheet 2 I have added a mapping at the top of each column for the Quarter, Year or Month that should be included in the Sheet 1 heading Row.

I tried using a sumproduct on both row and column using the whole data set underlying, but this did not work. "=SUMPRODUCT(1*('Sheet 2'!$A$9:$A$140='Sheet 1'!$C28)*('Sheet 2'!$E$9:$GA$9='Sheet 1'!E$27)*('Sheet 2'!$E$10:$GA$140))", it gives me a #N/A result.

I have tried to attach a table of what I am trying to do.
Sheet 1

Choose Frequency 1 Annual 1 2001 2002 2003 2004 2005 2006 2007 2008 Quarterly 2 12001 22001 32001 42001 12002 22002 32002 42002 Monthly 3 3/1/2001 4/1/2001 5/1/2001 6/1/2001 7/1/2001 8/1/2001 9/1/2001 10/1/2001 Cash Flow Annual 2/1/2001 2001 2002 2003 2004 2005 2006 2007 2008 Rental Revenue Other Revenue Recoveries Income Utilities Repairs & Maintainence Insurance Property Taxes Advertising & Upkeep General & Administrative Operating Expenses <tbody> </tbody>

<tbody>
</tbody>

Sheet2

Owned Owned Owned Owned Owned Owned Owned Owned Owned Quarter 12011 12011 12011 22011 22011 22011 32011 32011 32011 Year 2011 2011 2011 2011 2011 2011 2011 2011 2011 Month Jan-11 Feb-11 Mar-11 Apr-11 May-11 Jun-11 Jul-11 Aug-11 Sep-11 Rental Revenue 4100-0105 Apt-Market Rent Income - - - - - - - - Rental Revenue 4100-0110 Apt-Gain/Loss-Lease Income - - - - - - - - Rental Revenue 4100-0120 Apt-MonthlyConcession Income - - - - - - - - Rental Revenue 4100-0130 Apt-Move In Discounts Income - - - - - - - - Rental Revenue 4100-0140 Apt-Employee Allow Income - - - - - - - - Rental Revenue 4100-0150 Apt-Model Unit Income - - - - - - - - Rental Revenue 4100-0210 Apt-Vacancy Loss - - - - - - - - Rental Revenue 4100-APTBADDEBT Apt-Bad Debt - - - - - - - - Rental Revenue 4100-BASERENT Base Rent - - - - - - - - Rental Revenue 4100-CREDLOSS Credit Loss - - - - - - - - Rental Revenue 4100-FREERENT Free Rent Income - - - - - - - - Rental Revenue 4100-PCTRENT Percentage Rent Income - - - - - - - - Rental Revenue 4100-RENTREL Rent Relief - - - - - - - - Rental Revenue 4100-VACALLOW Vacancy Allowance - - - - - - - - Rental Revenue 4111-ROOMREV Htl-Room Revenue - - - - - - - - Rental Revenue 4112-BANQ Htl-Banquet Revenue - - - - - - - - Rental Revenue 4112-BEV Htl-Beverage Revenue - - - - - - - - Rental Revenue 4112-FOOD Htl-Food Revenue - - - - - - - - Rental Revenue 4114-OTHOPP Htl-Other Op Dept Revenue - - - - - - - - Rental Revenue 4114-PARK Htl-Parking Revenue - - - - - - - - Rental Revenue 4114-PHONE Htl-Telephone Revenue - - - - - - - - Rental Revenue 4118-OTHREV Htl-Rental & Other Income - - - - - - - - Recoveries 4200-CAMREV CAM Revenue - - - - - - - - Recoveries 4201-OTHREC Other Recoveries - - - - - - - - Other Revenue 4301-0010 Advertising Revenue - - - - - - - - Rental Revenue 4301-0020 Apt-Application Revenue - - - - - - - - Rental Revenue 4301-0030 Apt-Cable TV Revenue - - - - - - - - Rental Revenue 4301-0050 Apt-Deposit Forfeited Revenue - - - - - - - - Rental Revenue 4301-0060 HVAC Revenue - - - - - - - - Rental Revenue 4301-0090 Apt-Lease Termination Revenue - - - - - - - - Rental Revenue 4301-0130 Apt-Month to Month Revenue - - - - - - - - Other Revenue 4301-0160 Parking Revenue - - - - - - - - Rental Revenue 4301-0170 Apt-Pet Revenue - - - - - - - - Other Revenue 4301-0180 Roof Lease Satelite Revenue - - - - - - - - <tbody> </tbody>

<tbody>
</tbody>

Hopefully you all can help me out. Let me know if it's not clear.

Many thanks,
Ryan

 

[Skybluekid]

Am I right is saying that depending on whether you choose Monthly, Quarterly or Annually you would like data to be returned?

 

[RBenny]

Yes, What I've tried to do on Sheet2 is to add a few rows that would help a sumif work, so if it's quarterly, then i want all rows that are 12001,22001,32001,42001, etc. and that data to be condensed by the tags in column A.

 

[RBenny]

Here's how I would ideally like it to work:

1) The user chooses on Sheet 1, in cell B1, their frequency. 1=Annual, 2=Quarterly and 3=Monthly and the column headings in the cash flow statement change to meet these needs.

What I've done is that on Sheet 1, in rowsC2:KS4 is built the headers for each frequency, based on the start date in cell B5. Then in row C5:KS5 I make the headings appear until year 2025 for any frequency choice. So this part is complete.

2) Then I need the data, always monthly, on Sheet 2 to sum into the appropriate call based on two factors: Factor a) the tagging on column A in Sheet1 should sumif it matches the tagging on column A on sheet 2 and Factor b) the frequency as I've defined on Sheet1, Row C5:KS5.

a) What I've done here is to tag the rows in Sheet 2, Column A to match the rows in Sheet 1 Column A, to me this should work in a sumif. b) Then what I've done (and I'm not sure if this would be necessary) is to tag on Sheet 2 rows D7:GA9 the same way that the heading row in Sheet 1, row C5:KS5. This is where I was hoping that the tagging could rollup into the frequency. For example, on Sheet 2, you'll seee all three months Jan, Feb, Mar in Q1, but on Sheet 1 I want those months columns to combine into the single column for Q1.

Is this more clear?

 

[arcrex]

can you help me with this also please reply by email at ewahmada@gmail.com[IMG]C:\Users\Acer\Downloads\untitled.png[/IMG]

name	date	due	renew	p	p	p	p	p	p
			30	10	10	renew	10	10	10
			40	10	renew	10	10	10	10

<tbody>
</tbody>

i would like to sum up a row and stop summing up after it has renew