0

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.

  • 1
    Is there a reason you can't just filter on the second column? What happens when you have days in different months? do both 15/8 and 15/9 get listed as 15? For your second question COUNTIFS should do the job – cybernetic.nomad Aug 9 '18 at 17:32
  • 2
    Also which platform do you want? All three will have a different approach. – Scott Craner Aug 9 '18 at 17:47
  • 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. – cukabeka Aug 10 '18 at 16:28
  • The platform should be LibreOffice. If that might not be possible, I'd be willing to switch. Otherwise, I definitely prefer LibreOffice/OpenOffice. – cukabeka Aug 10 '18 at 16:29
  • different months don't occur in this scenario. I'll update the question accordingly. – cukabeka Aug 10 '18 at 16:30
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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.