Totals based on dates
 

Hello! I have a spreadsheet where I want to total a column based on
entries being between two dates (I'm in the UK, so the date format is
DD/MM/YYYY).

My spreadsheet is as follows:

Column A contains the date of the transaction
Column B contains the type of the transaction
Column C contains an amount (*it is this column that I would like to
total*)

An example spreadsheet would be:

01/01/2005 Standard 25.00
15/01/2005 Advanced 110.00
01/02/2005 Standard 50.00
09/02/2005 Advanced 15.50
01/03/2005 Standard 2.00
01/04/2005 Standard 55.00
01/05/2005 Standard 43.00

I would like the total of column C whe

1) All entries are between 1st January and 31st March and are
"Standard" - answer above would be 77.00

2) All entries are between 1st January and 31st March and are
"Advanced" - answer above would be 125.50

I would be grateful for your help!


--
DJ Dusty
------------------------------------------------------------------------
DJ Dusty's Profile: http://www.excelforum.com/member.php...o&userid=16335
View this thread: http://www.excelforum.com/showthread...hreadid=314067

=SUMPRODUCT(--(A1:A7=DATE(2005,1,1)),--(A1:A7<=DATE(2005,3,31)),--(B1:B7="Standard"),C1:C7)

OR

=SUMPRODUCT(--(A1:A7=D1),--(A1:A7<=E1),--(B1:B7=F1),C1:C7)

...where D1 contains the start date, E1 the end date, and F1 the
transaction type.

Hope this helps!

DJ Dusty Wrote:
Hello! I have a spreadsheet where I want to total a column based on
entries being between two dates (I'm in the UK, so the date format is
DD/MM/YYYY).

My spreadsheet is as follows:

Column A contains the date of the transaction
Column B contains the type of the transaction
Column C contains an amount (*it is this column that I would like to
total*)

An example spreadsheet would be:

01/01/2005 Standard 25.00
15/01/2005 Advanced 110.00
01/02/2005 Standard 50.00
09/02/2005 Advanced 15.50
01/03/2005 Standard 2.00
01/04/2005 Standard 55.00
01/05/2005 Standard 43.00

I would like the total of column C whe

1) All entries are between 1st January and 31st March and are
"Standard" - answer above would be 77.00

2) All entries are between 1st January and 31st March and are
"Advanced" - answer above would be 125.50

I would be grateful for your help!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=314067