Anders Asks: Find first and last of filter by date

Beginner

About this Tutorial

Useful for calculation or filters by dates.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

COUNT() IF() INDEX()

Video Transcript

<div>0:00 Well, member Anders asks this question and I thought it was really interesting answer and really useful for those who are doing sort of calculations or filters by dates. 0:13 So the question is he has a sort of this transactional sheet, and he's trying to filter it and I put them all on the same sheet, but sometimes you'll want all your transactions by date or timestamp on one tab. 0:28 And then you want to like select and filter it on another tab, but I'm going to put it, do it all in one for you to see it. 0:35 So he has these dates and each date has a transaction, but some dates there's multiple transactions.

What he's looking to do is it's really easy to set up a filter for this information, say, you want to select a date let's do 24 and you get the date it's and it really is just filtering C and 0:57 D. And you're just doing G2, which is the date is equal to the column C to see where you have the dates and you get this nice filter where you have the date and the transaction. 1:12 But two things.

One Anders is only looking for the number he wants to select the date and get a number . 1:19 There are however, some dates that have multiple numbers and really from those dates, he just wants the first one. But in addition to that, sometimes if it is the if it is this sort of array of transactions, he wants the first and the last. 1:37 So this filter doesn't do it, right? This filter shows us the date of the transaction.

And also if we select the date that has multiple, it shows us everything. 1:47 What we need to do here is use index and what we can, we can use index to wrap around this. 1:51 And I want to first share with you not what index does, but how would this filter works? This filter is an array of rows and columns, just like a sheet and index does this index is like, let's say a one, two D 10, and we use row one. 2:18 We want column three is going to be the word date, right? So that's row one, column three date. That's how index works. 2:28 So we can use that also on this, these answers.

So let's do that. So let's put the index Brown, we're going to use row one, column two to get that second number . 2:43 Cause we're doing getting C to D right? Let's do that. And we got 90 and that is the first answer here. 2:51 That's pretty simple, right? But we want also now that's works. What's interesting is this works on every date because every date has one until you have something that until you have a date that has known, but there every date has one. 3:06 The last one, however, is going to be more complicated because we can't this, this row one.

We, how do we know how many rows there are? 3:18 Well, we have this filter already here and we can do count. We can count how many things are there, count all, but we're going to get an error here. 3:31 And that error is referenced. It it's outside the balance. It's because this C to D is not really what we're counting. 3:38 Let's just count. We want the C to C we want that count and we still get an error because probably, Oh, I still have the C to C. 3:56 Now we're just counting the answers in this one. We don't have a two dimensional array.

We have a one dimensional array. 4:02 Now we have a clear count and let's see what we get. We get 65, which is the same thing. There let's go to two 26. 4:13 Now we get 90 here and the last one, 45. So now we know what the first transaction and the last year has action. 4:19 Or sometimes these are the same. And I'm not going to go through that in this video. How we figure that out, actually, actually, this is pretty simple. 4:27 Let me go over this. Cause this will show up.

This will be a question you're asking is on the dates that have only one. 4:35 How do you know that the first and last transaction are like the same number , but how do you know that those are the same actual answer? 4:43 There's only one row here. What you can do is because we are counting. We can set up a little if so if a logical expression , this is greater less than less than one, no, less than two equals one. 5:04 Let's do equals one, do nothing. Right? So only on the dates. There we go. So this will only show up when there is more than one row. 5:20 There you go.

So this first, last is nothing. What you might want to do though, is within the here, be like, no, which means not. 5:30 There is nothing. Not, not that it's zero. Not that it's nothing. It is just doesn't exist or you'll want to put something else here. 5:37 But that right here between these two cop commas is where you put the answer that you want to show again. 5:43 You can put literally nothing, but it's probably better to show that, Hey, the answer is that there's nothing here. So when it's two 26, let's double check. 5:55 There it is 98, 45. And there you go.

That gets you index and filter and count hall and F we'll get you the answer here, bite.</div>