conditional "less than A but more than B" (dates) 2

[tilltek]

I'm new to Excel (2000) and find it quite easy to use but I have stumbled on what should be an easy problem.
column A1 to A1000 hold a date.
column B2 to B1000 holds a dollar value.
I want to put a formula into a cell that sums column B only between certain dates (from column A).
01-01-2001 100.00
03-02-2001 50.00
14-02-2001 29.00
05-05-2001 123.00
13-07-2001 23.00
If i want to see the total for the first quarter of 2001 I need to sum the dollars only if the coresponding date is between 01-01-2001 and 31-03-2001, in this case $179.00
Any and all help would be apreciated.
Ken F

 

[Dreamboat]

Check this page out, Ken:

http://www.cpearson.com/excel/array.htm

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[Hasit]

You could also use "Conditional Sum" to achieve what you are trying to through Excel. It will take you through a GUI wizard and create an array formula very simply.

Make sure you have through TOOLS->ADD-INS checked the "Conditional Sum" box. Then through TOOLS->WIZARD, you can go through the Conditional Sum process.

 

[tilltek]

Thanks Hasit and Dreamboat but my problem is that I can't work out how to do it.
For instance, I tried the Conditional sum wizard and I could see no way of selecting dates.
I'm looking for something like...
SUM COLUMN B FOR BETWEEN(A,01-01-2001,30-01-2001) (Foxpro)
This is to be part of a simple expense report where enties are made as needed and then simple reports generated as needed EG: What did I spend in the first 1/4 of 2001.
I'm not sure whether I'm drinking too much or not enough but I just can't seem to switch between database programming and spreadsheets (I make my living with (or in spite of) Microsoft Foxpro).
Any chance you could let me have the formula I need?

 

[roscok]

Ken,

I have this working with two extra columns (they can be hiden if necessary)and the formula below:

In this example there is a column between the two sets of numbers (5 columns in total)

In the fourth column: =IF(AND(A1>=$E$1,A1<=$E$3),B1,0)

The $E$1 & $E$3 are referencing the dates in the far right column

For the First Quarter sum: =SUM(D15)


1/01/2001 100 100 1/01/2001
3/02/2001 50 50 31/03/2001
14/02/2001 29 29 1/04/2001
5/05/2001 123 0 30/06/2001
13/07/2001 23 0

First Quarter 179

hope this helps

Ross

 

[Hasit]

Ken,

Using your table below, the conditional sum formula is as follows:

D E
1 Date Value
2 01/01/2001 100
3 03/02/2001 50
4 14/02/2001 29
5 05/05/2001 123
6 13/07/2001 23

First Quarter Spend: 179

Note that I have given each column a title (you need this if you are going to use the conditional sum wizard). The wizard then generates the following formula:

{=SUM(IF($D$2:$D$6>=DATEVALUE(&quot;01/01/2001&quot,IF($D$2:$D$6<=DATEVALUE(&quot;14/02/2001&quot,$E$2:$E$6,0),0))}

Note that curly brackets around the formula. These are not typed in, they are vreated using CTRL, SHIFT and ENTER. (For more details on this check out this link from JV Friederick:

http://www.tek-tips.com/faqs.cfm?spid=68&newpid=68&sfid=660&CFID=26969603&CFTOKEN=81607552)

I have in the above formula explcitly put in the cell references for the quarter.

Hope that helps.

 

[tilltek]

Thanks to one and all. At last I've got it (and it works).