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
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 802"]
<tbody>[TR]
[TD]Choose Frequency[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Annual[/TD]
[TD]1[/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]2007[/TD]
[TD]2008[/TD]
[/TR]
[TR]
[TD]Quarterly[/TD]
[TD]2[/TD]
[TD]12001[/TD]
[TD]22001[/TD]
[TD]32001[/TD]
[TD]42001[/TD]
[TD]12002[/TD]
[TD]22002[/TD]
[TD]32002[/TD]
[TD]42002[/TD]
[/TR]
[TR]
[TD]Monthly[/TD]
[TD]3[/TD]
[TD="align: right"]3/1/2001[/TD]
[TD="align: right"]4/1/2001[/TD]
[TD="align: right"]5/1/2001[/TD]
[TD="align: right"]6/1/2001[/TD]
[TD="align: right"]7/1/2001[/TD]
[TD="align: right"]8/1/2001[/TD]
[TD="align: right"]9/1/2001[/TD]
[TD="align: right"]10/1/2001[/TD]
[/TR]
[TR]
[TD]Cash Flow Annual[/TD]
[TD]2/1/2001[/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]2007[/TD]
[TD]2008[/TD]
[/TR]
[TR]
[TD]Rental Revenue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Other Revenue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Recoveries[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Income[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Repairs & Maintainence[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Property Taxes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Advertising & Upkeep[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]General & Administrative[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Operating Expenses[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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


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]
[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]date[/TD]
[TD]due[/TD]
[TD]renew[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]renew[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40[/TD]
[TD]10[/TD]
[TD]renew[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

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