PLEASE HELP!!! SUMIF and SUBTOTAL NIGHTMARE

[SpreadsheetPhil]

I'm trying to SUM the financial data in a column on an Excel Data Table if it has a date within another column that falls between 2 dates. I've tried the below formula, but it just keeps on returning £0.00. No idea where I'm going wrong. Can anyone help?

=SUMPRODUCT(SUBTOTAL(9,OFFSET($G$2:$G$2654,ROW($G$2:$G$2654)-MIN(ROW($G$2:$G$2654)),,1)),--($D$2:$D$2654>=1/1/2014),--($D$2:$D$2654<=31/12/2014))

 

[KRice]

I believe the issue may be in your data format. Try this:
=SUMPRODUCT(($D$2:$D$2654>=DATE(2014,1,1))*($D$2:$D$2654<=DATE(2014,12,31))*($G$2:$G$2654))

 

[SpreadsheetPhil]

You're almost there. It now sums correctly. However, I can't then get a SUBTOTAL if I filter on the table. Any thoughts on how I can add that in as that is the critical element? I've tried it like this, but it won't work - says 'Too few arguments':

=SUMPRODUCT(SUBTOTAL(9,OFFSET(($M$2:$M$2654>=DATE(2014,1,1))*($M$2:$M$2654<=DATE(2014,12,31))*($F$2:$F$2654))

 

[KRice]

I'm not following. My earlier suggestion creates arrays that perform the logical tests to determine if the date criteria are met, essentially creating a logical filter that when multiplied by the financial data array and then summed, the result is the subtotal (i.e., the total of only the data meeting the filtering criteria). Do you wish to apply an actual "filter" to the table also? If so, then I see your point...as the SUMPRODUCT formula doesn't "see" the other filter. An advanced filter may address your needs and offer more flexibility, as other criteria can be easily added. See one implementation below. Shown is the moment before application of the advanced filter. After application [under Data > Advanced Filter (Sort & Filter block) > and then highlighting the data table (A5:C13) and filter criteria (E1:G2) and selecting the option to filter in place], the table collapses to show only the rows whose date criteria are met. The subtotal formula in A3 will update to show the subtotal for only the displayed rows. I've included the earlier SUMPRODUCT formulas (one with the desired date limits, the other with much wider limits) for comparison. Would this work better?

MrExcel_problem-solving.xlsx
	A	B	C	D	E	F	G	H
1	15110	<--subtotal for SUMPRODUCT			date	date	client
2	28609	<--total for SUMPRODUCT			>=1/1/2014	<=12/31/2014	*
3	28609	<--subtotal for other filter (either quick or adv)
4
5	Data	Date	Client
6	2500	12/15/2013	A
7	1000	12/31/2013	B
8	54	1/1/2014	C
9	789	6/15/2014	C
10	6543	9/15/2014	B
11	234	12/15/2014	A
12	7490	12/31/2014	A
13	9999	1/1/2015	C
14
Sheet12

Cell Formulas
Range	Formula
A1	A1	=SUMPRODUCT(($B$6:$B$2658>=DATE(2014,1,1))*($B$6:$B$2658<=DATE(2014,12,31))*($A$6:$A$2658))
A2	A2	=SUMPRODUCT(($B$6:$B$2658>=DATE(1900,1,1))*($B$6:$B$2658<=DATE(2100,12,31))*($A$6:$A$2658))
A3	A3	=SUBTOTAL(9,A6:A2658)

 

[Eric W]

You're almost there. It now sums correctly. However, I can't then get a SUBTOTAL if I filter on the table. Any thoughts on how I can add that in as that is the critical element? I've tried it like this, but it won't work - says 'Too few arguments':

=SUMPRODUCT(SUBTOTAL(9,OFFSET(($M$2:$M$2654>=DATE(2014,1,1))*($M$2:$M$2654<=DATE(2014,12,31))*($F$2:$F$2654))

I think you're looking for:

=SUMPRODUCT(SUBTOTAL(103,OFFSET($M$2,ROW($M$2:$M$2654)-ROW($M$2),0))*($M$2:$M$2654>=DATE(2014,1,1))*($M$2:$M$2654<=DATE(2014,12,31))*($F$2:$F$2654))

 

[KRice]

Eric,
Thanks for offering this nice solution to compute the sum of a data column in a table with constraints/filters.