Efficient use of SUMPRODUCT to SUM Year to Date values based on current MONTH

[correll26]

Hello Excel Ninjas!


This is my first post after doing a search for the solution. I am a very intermediate Excel user doing a dashboard for a work project. To give you some perspective, I just started using VLOOKUP 3 weeks ago.


Everything has been going smoothly, but I have hit an obstacle I can't seem to get over.


Starting with the attached raw data (it is a snippet of the 4,000 lines for the full data set), I have been using helper cells (columns A, B and C) and VLOOKUP to pull monthly NET INCOME AFTER DEBT, among other values.


There is a 2007 and 2003 version: https://www.dropbox.com/sh/wztj9hw3yf4apr4/qSuJ9Z23cW


However, now I need to total the months through the CURRENT DATE based on that selection. I started with the unwieldy formula below, where $E$5 is CURRENT DATE.


=IFERROR(CHOOSE($E$5,SUM(VLOOKUP("NET INCOME AFT DEBT SRVC"&$E7,data_BUDGET!$C$1:$S$3768,{3},FALSE)),SUM(VLOOKUP("NET INCOME AFT DEBT SRVC"&$E7,data_BUDGET!$C$1:$S$3768,{3,4},FALSE)),SUM(VLOOKUP("NET INCOME AFT DEBT SRVC"&$E7,data_BUDGET!$C$1:$S$3768,{3,4,5},FALSE)),SUM(VLOOKUP("NET INCOME AFT DEBT SRVC"&$E7,data_BUDGET!$C$1:$S$3768,{3,4,5,6},FALSE))),0)


This works, but is a very long formula and as you can see it does not even cover all 12 months.


I thought the SUMPRODUCT formula might be handy here and tested it on another sheet. When I tested it, the formula below works great, where $E$5 is the MONTH value for the current date:


=SUMPRODUCT(--(D6:O6<=$E$5),D8:O8)


My problem is, I need to somehow combine this with VLOOKUP of NET INCOME AFTER DEBT so it reads the correct row of data to another sheet.


Can anyone offer a concise way to do this?

 

[Luke M]

I can't access your file from my location, but I'll take a guess at the formula structure. When dealing with dates, it's often helpful to use the TEXT function, which hopefully you'll see why in a bit. Let's say you have the date June 16, 2013 in cell A2, and you want to sum all the data for month of June.


=SUMPRODUCT((TEXT(DateRange,"mmyyyy")=TEXT(A2,"mmyyyy")*(SomeNumberRange))

The Text function uses same structure as cell formats, so you can change it to any combination of day/month/year to get what you want.

For you data, I'm guessing you have months in different columns, but there is a specific row you want.

=SUMPRODUCT(($C$2:$C$3768=$E7)*(TEXT($D$1:$S$1,"yyyymmdd")<TEXT(TODAY(),"yyyymmdd"))*($D$2:$S$3768))


If I'm completely wrong, I have a feeling one of the other Ninjas will be by shortly who doesn't suffer from CASFFML and will be able to finish this. =/

 

[correll26]

Luke - thanks for your response. I found that the formula below will do the work if it is on the same sheet.


=SUMPRODUCT(--(DatesInMonths<=CurrentMonth),MonthlyNetIncome)


However, what I need is to pull these totals into another sheet after finding the "NET INCOME AFTER DEBT" row on the table. That is why I was inclined towards using VLOOKUP but I am having trouble figuring out how to combine SUMPRODUCT and VLOOKUP. For reference, I added an Excel 2003 version to the dropbox link.

 

[SirJB7]

@Luke M

Hi!

Your slave secretary falls at your feet praising to god Carlsberg to cure you from your severe CASFFML illness and humbly posts the range A44:J44 (10 columns).

-----

[pre]

A	B	C			D			E	F	G	H	I	J
A		NET OPERATING INCOMEA	NET OPERATING INCOME	25.723	54.179	66.484	68.954	17.120	67.588

[/pre]
-----

Regards!

 

[Luke M]

@SirJB7

Would have been nice to see more of the sheet, including the row of interest, but I'll take another shot...


Perhaps something then like:

=SUMPRODUCT((data_BUDGET!$D$2:$D$5000="NET INCOME AFTER DEBT")*(data_BUDGET!$E$1:$S$1<=CurrentMonth)*(data_BUDGET!$E$2:$S$5000))


I'm still struggling to understand where the month labels are, what your criteria value looks like, and how the specific row labels are, but this should get a little closer.

 

[NARAYANK991]

Hi Correll ,


I am not able to understand what your requirement is.


Two things :


1. A formula like this :


=(--(MonthValue<=D3))


which is in D18 , should be entered as an array formula , using CTRL SHIFT ENTER.


2. The formula in D17 should be changed to :


=SUMPRODUCT((data!$E44:INDEX(data!$44:$44,D3+4)))


Narayan

 

[SirJB7]

@Luke M

Hi!


Okay, okay, you ask for it, you get it, range A1:J50


-----

[pre]

MONTH VALUE:	1	2	3	4	5	6
Database:	HMP	Forecast - Budget Report	Page:	1
ENTITY:	301001	Property Owner, LLC	Date:	41443
Time:	0,747222222				

Cash
Actual amounts from 01/13 to 05/13, Budget amounts from  06/13 to 12/13						

Total	Total
ene-13	feb-13	mar-13	abr-13	may-13	jun-13	jul-13
INCOME
41100-010	Base Rent-Office	0	283	283	283	-848
42100-010	Base Rent-Retail	57.799	55.984	56.549	56.267	57.397
47200-010	R/E Tax Reimb	16.280	15.344	17.216	16.280	16.280
47999-010	Unapplied Cash	15886	-15886	0	0	0

TOTAL INCOME	89.965	55.724	74.048	72.829	72.829	72.547

OPERATING EXPENSES
REIMBURSABLE EXPENSES
67105-010	Real Estate Tax	50218	0	0	0	50.218

TOTAL REIMBURSABLE EXPS	50.218	0	0	0	50.218	0
NON-REIMBURSABLE EXPS
65235-020	NR Fire & Life Safety R	0	0	0	0	0
65720-010	NR Salaries	1.417	0	3.148	1.574	0
66010-012	NR Prof Fee-Acctng & Au	175	0	0	0	0
66040-010	NR Prof Fees-Arch & Eng	6000	0	0	0	0
66060-010	NR Legal Fees	1041	0	0	0	0
66130-013	NR Prop Management Fee	2.876	1.482	1.438	1.475	1.457
66170-080	NR Signs	0	0	2978	0	3.171
66170-090	NR Marketing Miscellane	0	0	0	0	450
66175-010	NR Ins-Gen Liability&Um	823	0	0	165	165
66175-011	NR Ins-Prop/All Rsk/Ren	453	0	0	0	0
66175-021	NR Ins-Pollution Liabil	1240	0	0	661	248
66230-011	NR Computer HW/SW R&M	0	0	0	0	0
66300-019	NR Travel-Other	0	63	0	0	0

TOTAL NON-REIMB EXPS	14.025	1.545	7.563	3.875	5.491	4.959

TOTAL OPERATING EXPENSES	64.243	1.545	7.563	3.875	55.709	4.959

NET OPERATING INCOME	25.723	54.179	66.484	68.954	17.120	67.588

DEBT SERVICE
25600-010	Mortgage Payable 1	7.739	7.739	7.810	7.881	7.359
69520-014	Int Exp-1st/Wrap	21.277	21.277	21.205	21.134	21.656

TOTAL DEBT SERVICE	29.016	29.016	29.016	29.016	29.016	29.016

[/pre]
-----


Regards!