SUMPRODUCT or SUM Formula with Date Range

[BillNLTC]

Hi Folks...I hope you can help me (as you have so remarkably in the past!) with a challenge.

I have a spreadsheet with 3 columns:
Column A contains dates in this format: 1/15/06
Column B contains either "YES" or "NO"
Column C contains dollar amounts.
Columns D through O are labeled January through December

For those items containing a "YES" in Column B, the corresponding dollar amounts in Column C must be summed in a cell for the month corresponding to their date.

For instance, if there are three sales for January (say, on the 15th, 18th, and 26th) listed in Column A, and they all have a "YES" in Column B, their corresponding dollar amounts need to be summed and placed into a single cell under Column D-January.

Two additional points:
Point 1: The date ranges that I need to use do not correspond exactly with calendar dates. In other words, for my purposes, the month of January ends on the 28th so I need to capture only those amounts that have a date prior to the 29th. February ends on the 25th, but also includes January 29th, 30th, and 31st. And so on.

Point 2: I have determined that the problem with the formulas below has something to do with the date range. If I substitute any other character, number, or word for the date range, it works perfectly. I tried using a VLOOKUP to substitute a number for the date range, but I can't get the formulas to work with it.

I have tried the following two formulas with no luck:

{=SUM(($A$3:$A$1000<"1/29/06")*($B$3:$B$1000="YES")*($C$3:$C$1000))}

=SUMPRODUCT(($A$3:$A$1000<"1/29/06")*($B$3:$B$1000="YES")*($C$3:$C$1000))

I appreciate any advice that you may have, and thank you for your time and consideration!

 

[Krishnakumar]

Hi BillNLTC,

Welcome to MrExcel!

Book1
	A	B	C	D	E	F
1			1-Jan	28-Jan	25-Feb	31-Mar
2				Jan	Feb	Mar
3	01/05/06	Yes	$100	400	1600	1800
4	01/15/06	No	$200
5	01/25/06	Yes	$300
6	01/30/06	Yes	$400
7	02/04/06	Yes	$500
8	02/14/06	No	$600
9	02/24/06	Yes	$700
10	02/27/06	Yes	$800
11	03/06/06	No	$900
12	03/16/06	Yes	$1,000
13	03/26/06	No	$1,100
Sheet3

Formula in D3 and copy across,

=SUMPRODUCT(--($A$3:$A$19>=C$1),--($A$3:$A$19<=D$1),--($B$3:$B$19="Yes"),$C$3:$C$19)

D1:F1 houses the month end dates.

HTH

 

[Aladin Akyurek]

Book6
	A	B	C	D	E	F	G
1
2	Date	Y/N	Amount	28-Jan-05	25-Feb-05	28-Mar-05	28-Mar-05
3	1/3/2005	Y	67.16	133.2	226.87	121.25	121.25
4	1/24/2005	Y	66.04
5	1/26/2005	N	32.44
6	1/29/2005	Y	86.72
7	1/30/2005	Y	39.48
8	2/10/2005	Y	19.17
9	2/17/2005	Y	81.5
10	2/28/2005	N	81.64
11	2/28/2005	Y	16.54
12	3/1/2005	Y	55.5
13	3/14/2005	N	17.26
14	3/16/2005	Y	65.75
Sheet1

D2, copied across:

=SUMPRODUCT(--($B$3:$B$14="Y"),--($A$3:$A$14>=EDATE(D$2,-1)+1),--($A$3:$A$14<=D$2),$C$3:$C$14)

 

[BillNLTC]

Thanks a million Aladin and Krishnakumar!!! I'm ashamed to say that I've spent about 10 hours, mostly reviewing previous board posts, doing what it took you mere minutes to do! I'm sincerely grateful.

If I might ask, I'm not familiar with the two dashes that you've used in your formulas. If your time permits, is a brief explanation possible?

Again, thank you so much for your expertise in resolving this for me. Take care...

 

[Krishnakumar]

Hi,

See this

HTH