spreadsheet formula for returning parts of filtered cells in column

Question

I use Libreoffice. (If Excel or GoogleSpreadsheets would be the only way to get a solution, I would be willing to switch. But the preferred platform is LibreOffice, I'm looking for a solution on this Software).

Here's what I want to achieve, I have a spreadsheet like this:

|Date         |Available?   |Length  |
|2/8 9:00     |no           |10
|26/8 9:00    |yes          |5
|28/8 9:00    |no           |2
|29/8 8:00    |yes          |5.5
|30/8 9:00    |yes          |6
|31/8 9:00    |no           |3

Now I want to extract all dates where there is a YES in the "available" column. Different months don't occur in this scenario, so only the day is relevant. What I want to have as a result in some other part of the spreadsheet would be

26, 29, 30

And to make it even a bit more complex, what if I want to count the number of days where length is >5 and available is true? In the above example, this would be

3

Is that possible? I doodled around with VLOOKUP but have no idea where to start. I guess that Excel would work the same or similar.

I want this to work without the need for the user to filter anything by hand. The idea is to just copy/paste the data and get the result via the formula in another sheet of the file and create an invoice there.

Answer 1

In excel... & based on this good index() + match() reference.

The solution for this part :

"if I want to count the number of days where length is >5 and available is true? > 3 "

if length is >5 , then you should get 2.. not 3.. if length is >=5, then only you'll get 3.

Assuming your sample data is in A1:C7 , the answer is :

=COUNTIFS(B:B,"yes",C:C,">5")

You'll get 2.

and for this part :

"want to extract all dates where there is a YES in the "available" column "

Assuming your "2/8 9:00" data is located at A2 and "31/8 9:00" is located at A7, all data Id column A is formatted as date/time format in excel, put this in D2 :

=IFERROR(IF(ROWS($A$2:$A2)>1,INDEX(OFFSET($A$2:$A$7,MATCH(D1,$A$2:$A$7,0),0),MATCH("yes",OFFSET($B$2:$B$7,MATCH(D1,$A$2:$A$7,0),0),0)),INDEX($A$2:$A$7,MATCH("yes",$B$2:$B$7,0))),"")

and this in E2 :

=IFERROR(DAY(D2),"")

you'll get the " 26, 29, 30 " there.