can I do this in excel
 

I have the following columns in a worksheet
A B C D E
DATE ITEM COST TAX TOTAL COST
28/04/07 LAYING PIPE $50.00 $5.00 $55.00
THROUGH TO
6/5/07 LAYING PIPE $50.00 $5.00 $55.00

can excel calculate the total cost for any period for example say from
28/04/07 to 06/05/07. I have treid sumif and vlookup but cannot get a formula
to calculate between the dates specified.
Is there a formula that I could use to calculate from and to or between dates.
thank you for your advice.

can I do this in excel
 

=SUMPRODUCT(--(A2:A20=--"2007-04-28"),--(A2:A20<=--"2007-05-06"),E2:E20)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"bee" wrote in message
...
I have the following columns in a worksheet
A B C D E
DATE ITEM COST TAX TOTAL COST
28/04/07 LAYING PIPE $50.00 $5.00 $55.00
THROUGH TO
6/5/07 LAYING PIPE $50.00 $5.00 $55.00

can excel calculate the total cost for any period for example say from
28/04/07 to 06/05/07. I have treid sumif and vlookup but cannot get a
formula
to calculate between the dates specified.
Is there a formula that I could use to calculate from and to or between
dates.
thank you for your advice.

can I do this in excel
 

Yes it can, Try:-

=SUMPRODUCT(--(A1:A5=DATEVALUE("20/04/07")),--(A1:A5<=DATEVALUE("05/06/07")),D1:D5)

Mike

"bee" wrote:

I have the following columns in a worksheet
A B C D E
DATE ITEM COST TAX TOTAL COST
28/04/07 LAYING PIPE $50.00 $5.00 $55.00
THROUGH TO
6/5/07 LAYING PIPE $50.00 $5.00 $55.00

can excel calculate the total cost for any period for example say from
28/04/07 to 06/05/07. I have treid sumif and vlookup but cannot get a formula
to calculate between the dates specified.
Is there a formula that I could use to calculate from and to or between dates.
thank you for your advice.

can I do this in excel
 

Thank you very much for your reply.

Could you tell me what the (-- means in the formula.
I appreciate your solution to my problem.

"Mike H" wrote:

Yes it can, Try:-

=SUMPRODUCT(--(A1:A5=DATEVALUE("20/04/07")),--(A1:A5<=DATEVALUE("05/06/07")),D1:D5)

Mike

"bee" wrote:

I have the following columns in a worksheet
A B C D E
DATE ITEM COST TAX TOTAL COST
28/04/07 LAYING PIPE $50.00 $5.00 $55.00
THROUGH TO
6/5/07 LAYING PIPE $50.00 $5.00 $55.00

can excel calculate the total cost for any period for example say from
28/04/07 to 06/05/07. I have treid sumif and vlookup but cannot get a formula
to calculate between the dates specified.
Is there a formula that I could use to calculate from and to or between dates.
thank you for your advice.

can I do this in excel
 

Bee,

have a look here where Chip Pearson explains it far better then I ever could.

http://www.mcgimpsey.com/excel/variablerate1.html

Mike



"bee" wrote:

Thank you very much for your reply.

Could you tell me what the (-- means in the formula.
I appreciate your solution to my problem.

"Mike H" wrote:

Yes it can, Try:-

=SUMPRODUCT(--(A1:A5=DATEVALUE("20/04/07")),--(A1:A5<=DATEVALUE("05/06/07")),D1:D5)

Mike

"bee" wrote:

I have the following columns in a worksheet
A B C D E
DATE ITEM COST TAX TOTAL COST
28/04/07 LAYING PIPE $50.00 $5.00 $55.00
THROUGH TO
6/5/07 LAYING PIPE $50.00 $5.00 $55.00

can excel calculate the total cost for any period for example say from
28/04/07 to 06/05/07. I have treid sumif and vlookup but cannot get a formula
to calculate between the dates specified.
Is there a formula that I could use to calculate from and to or between dates.
thank you for your advice.

can I do this in excel
 

No he doesn't!!! this is JE McGimpsey's site.

Mike

"Mike H" wrote:

Bee,

have a look here where Chip Pearson explains it far better then I ever could.

http://www.mcgimpsey.com/excel/variablerate1.html

Mike



"bee" wrote:

Thank you very much for your reply.

Could you tell me what the (-- means in the formula.
I appreciate your solution to my problem.

"Mike H" wrote:

Yes it can, Try:-

=SUMPRODUCT(--(A1:A5=DATEVALUE("20/04/07")),--(A1:A5<=DATEVALUE("05/06/07")),D1:D5)

Mike

"bee" wrote:

I have the following columns in a worksheet
A B C D E
DATE ITEM COST TAX TOTAL COST
28/04/07 LAYING PIPE $50.00 $5.00 $55.00
THROUGH TO
6/5/07 LAYING PIPE $50.00 $5.00 $55.00

can excel calculate the total cost for any period for example say from
28/04/07 to 06/05/07. I have treid sumif and vlookup but cannot get a formula
to calculate between the dates specified.
Is there a formula that I could use to calculate from and to or between dates.
thank you for your advice.

can I do this in excel
 

The -- converts a TRUE/FALSE condition into a numeric 1/0 which allows
SUMPRODUCT to perform its calculations.

See this site for an excellent overview/explanation of SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"bee" wrote:

Thank you very much for your reply.

Could you tell me what the (-- means in the formula.
I appreciate your solution to my problem.

"Mike H" wrote:

Yes it can, Try:-

=SUMPRODUCT(--(A1:A5=DATEVALUE("20/04/07")),--(A1:A5<=DATEVALUE("05/06/07")),D1:D5)

Mike

"bee" wrote:

I have the following columns in a worksheet
A B C D E
DATE ITEM COST TAX TOTAL COST
28/04/07 LAYING PIPE $50.00 $5.00 $55.00
THROUGH TO
6/5/07 LAYING PIPE $50.00 $5.00 $55.00

can excel calculate the total cost for any period for example say from
28/04/07 to 06/05/07. I have treid sumif and vlookup but cannot get a formula
to calculate between the dates specified.
Is there a formula that I could use to calculate from and to or between dates.
thank you for your advice.