Wednesday, January 4, 2012

yogi_Count Trades By Specified Dates For BUY and SELL Attributes

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user lowsky13 said:
In the A column I want to have dates (mm/dd/yyyy). Column B should then look at another sheet and search for columns with that date. The following works if it is hardcoded in (I have to manually put in the dates in the date function because DATE can only be in yyyy;mm;dd format only and I have mm/dd/yyyy in my A column. Is there a way to update the below to dynamically use the column A date instead of hard coding it? The second date (in this case 2011;12;30) doesn't need to be there because I only care about the one date but I found the below solution that looks for a range as an example of how to do what I did so that is why there are two dates. It could only be one if it is easier to just rewrite it.

=COUNT(FILTER( trades!O:; trades!O:>=DATE( 2011;12;29) ; trades!O:<=DATE( 2011;12;30) ;SEARCH( "b" ; trades!I:) ) )
------------------------------------
following is solution to the problem: