google sheetsworksheet-function
I have the below formula to remove blanks in a list and would like to add a separate cell value at the end of it (let's call it A1), not related to range V9:V22, but I would like A1 to show up below the list this formula creates:
=filter(V9:V22,V9:V22<>"")
Any quick add on to connect A1 to the end of this?
Thanks!
In order to reproduce the result, given by the OP, you need to use this formula.
=SUM(FILTER(C2:C,B2:B=D6,A2:A>=D4,A2:A<=D5))
Apart from that, you need to change the end date from 7/20/2012 to 7/23/2012
See example file I prepared: SUM with FILTER
The match formula will tell you which column within 1:1 matches today(); in conjunction with index we can use that column number to get the address of the cell holding the Yes/No data for today. This becomes useful when paired through string concatenation (&) with the address of the first cell of the Yes/No data ($A$2 here) -- the 'dynamic' range you wanted can now be interpreted using the indirect formula!
All together, you're looking at the following formula for B3 in your example:
=COUNTIF(INDIRECT("$A$2:"&CELL("address",INDEX($1:$2,2,MATCH(TODAY(),$1:$1)))),"Yes")
edit
I temporarily forgot about filter, which makes this task much easier:
=COUNTIF(FILTER(2:2,1:1<=TODAY()),"No")
Best Answer
That was an obvious fix, I just extended the range and made the last value in the filter what I needed.I put A1 at the end of range V9:V22.