| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
