Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a date range to output values according to a constant.
This one has been bugging me all afternoon, I hope someone can help.
I am trying to put together a sales report from a big list of data. A chunk of the table looks like this: transaction-type transaction-date amount-gbp Buyer Payment 15/10/2006 19:29 5.49 Closing Fee 16/10/2006 12:47 -0.51 Funds Transfer Out 17/10/2006 00:00 -4.73 Withdrawal Fee 17/10/2006 00:00 -0.25 Buyer Payment 20/10/2006 17:59 4.49 Buyer Payment 23/10/2006 11:37 5.38 Closing Fee 23/10/2006 15:56 -0.42 Closing Fee 23/10/2006 15:56 -0.5 Funds Transfer Out 24/10/2006 00:00 -8.5 Withdrawal Fee 24/10/2006 00:00 -0.45 Buyer Payment 25/10/2006 18:31 3.99 Buyer Payment 25/10/2006 19:55 4.79 I am trying to produce a formulae that will be able to tell me the value of the sales for "Buyer Payment" within a certain date range. I've managed to successfuly use the formulae: =SUMIF($B$18:$B$20018,"<"&E2,$E$18:$E$20018) where "$B$18:$B$20018" is the range of dates in the table - E2 is the date to search greater than and $E$18:$E$20018 is the ammount_GBP but this only gives me an ammount for all transactions. How would I incorporate this formulae to show only the "Buyer Payments" over a date range. Many thanks. -- There Is No Spoon! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a date range to output values according to a constant.
hello geebz,
maybe i misunderstood your question 1. there is a certain "date range" say between dates stored on cells F2(as min.) and E2(as max.) =SUM(($A$18:$A$20018="Buyer Payment")*($B$18:$B$20018<=E2)*($B$18:$B$20018=F2 )*($E$18:$E$20018)) hit F2, and press ctrl+shift+enter : this is an array formula happy holidays... "Geebz" wrote: This one has been bugging me all afternoon, I hope someone can help. I am trying to put together a sales report from a big list of data. A chunk of the table looks like this: transaction-type transaction-date amount-gbp Buyer Payment 15/10/2006 19:29 5.49 Closing Fee 16/10/2006 12:47 -0.51 Funds Transfer Out 17/10/2006 00:00 -4.73 Withdrawal Fee 17/10/2006 00:00 -0.25 Buyer Payment 20/10/2006 17:59 4.49 Buyer Payment 23/10/2006 11:37 5.38 Closing Fee 23/10/2006 15:56 -0.42 Closing Fee 23/10/2006 15:56 -0.5 Funds Transfer Out 24/10/2006 00:00 -8.5 Withdrawal Fee 24/10/2006 00:00 -0.45 Buyer Payment 25/10/2006 18:31 3.99 Buyer Payment 25/10/2006 19:55 4.79 I am trying to produce a formulae that will be able to tell me the value of the sales for "Buyer Payment" within a certain date range. I've managed to successfuly use the formulae: =SUMIF($B$18:$B$20018,"<"&E2,$E$18:$E$20018) where "$B$18:$B$20018" is the range of dates in the table - E2 is the date to search greater than and $E$18:$E$20018 is the ammount_GBP but this only gives me an ammount for all transactions. How would I incorporate this formulae to show only the "Buyer Payments" over a date range. Many thanks. -- There Is No Spoon! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Display values set in a range?? | Excel Worksheet Functions | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Counting values within a Date Range | Excel Discussion (Misc queries) |