Trying to learn FILTER

[charlesstricklin]

Hey again everybody,

I've "upgraded" to Office 2010 (sorry, not going to pay for a subscription, at least not right now) and I'm trying to learn about the FILTER function. For example data, I have (in my sheet) columns G and H, beginning at 1:

Date	Amount
1/1/2023	$ 112.64
2/4/2023	$ 54.04
3/1/2023	$ 170.85
4/8/2023	$ 58.01
5/3/2023	$ 10.29
6/2/2023	$ 48.12
7/1/2023	$ 10.61
8/1/2023	$ 51.68
9/1/2023	$ 177.69
9/2/2023	$ 140.48
1/1/2024	$ 63.62
2/1/2024	$ 35.57
3/1/2024	$ 59.75
4/1/2024	$ 22.05
5/1/2024	$ 43.34
6/1/2024	$ 16.23
7/1/2024	$ 105.76
8/1/2024	$ 107.30
9/1/2024	$ 8.08
10/1/2024	$ 133.83
11/1/2024	$ 30.03
12/1/2024	$ 70.80

Given that the dates are in date format and not month or years by themselves, how would I use FILTER to display only contents by year, by month, or by year and month?

Many thanks in advance. I always learn something talking to you guys.

 

[Fluff]

The Filter function does not exist in 2010, it's only in 2021 & 365.

 

[alansidman]

Using Power Query. Total By Year

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Month Name", {"Year"}, {{"Total by Year", each List.Sum([#" Amount "]), type number}})
in
    #"Grouped Rows"

total by month

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Month Name", {"Month Name"}, {{"Total by Month", each List.Sum([#" Amount "]), type number}})
in
    #"Grouped Rows"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[charlesstricklin]

The Filter function does not exist in 2010, it's only in 2021 & 365.

I apologize, I had 2010, I've upgraded to 2021.

 

[Fluff]

I've upgraded to 2021.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[charlesstricklin]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I just did, thanks.

 

[Fluff]

For year how about

Fluff.xlsm
	G	H	I	J	K	L
1	Date	Amount
2	01/01/2023	112.64		2023	01/01/2023	112.64
3	04/02/2023	54.04			04/02/2023	54.04
4	01/03/2023	170.85			01/03/2023	170.85
5	08/04/2023	58.01			08/04/2023	58.01
6	03/05/2023	10.29			03/05/2023	10.29
7	02/06/2023	48.12			02/06/2023	48.12
8	01/07/2023	10.61			01/07/2023	10.61
9	01/08/2023	51.68			01/08/2023	51.68
10	01/09/2023	177.69			01/09/2023	177.69
11	02/09/2023	140.48			02/09/2023	140.48
12	01/01/2024	63.62
13	01/02/2024	35.57
14	01/03/2024	59.75
15	01/04/2024	22.05
16	01/05/2024	43.34
17	01/06/2024	16.23
18	01/07/2024	105.76
19	01/08/2024	107.3
20	01/09/2024	8.08
21	01/10/2024	133.83
22	01/11/2024	30.03
23	01/12/2024	70.8
24
Data

Cell Formulas
Range	Formula
K2:L11	K2	=FILTER(G2:H100,YEAR(G2:G100)=J2)
Dynamic array formulas.

 

[charlesstricklin]

For year how about

Fluff.xlsm
	G	H	I	J	K	L
1	Date	Amount
2	01/01/2023	112.64		2023	01/01/2023	112.64
3	04/02/2023	54.04			04/02/2023	54.04
4	01/03/2023	170.85			01/03/2023	170.85
5	08/04/2023	58.01			08/04/2023	58.01
6	03/05/2023	10.29			03/05/2023	10.29
7	02/06/2023	48.12			02/06/2023	48.12
8	01/07/2023	10.61			01/07/2023	10.61
9	01/08/2023	51.68			01/08/2023	51.68
10	01/09/2023	177.69			01/09/2023	177.69
11	02/09/2023	140.48			02/09/2023	140.48
12	01/01/2024	63.62
13	01/02/2024	35.57
14	01/03/2024	59.75
15	01/04/2024	22.05
16	01/05/2024	43.34
17	01/06/2024	16.23
18	01/07/2024	105.76
19	01/08/2024	107.3
20	01/09/2024	8.08
21	01/10/2024	133.83
22	01/11/2024	30.03
23	01/12/2024	70.8
24
Data

Cell Formulas
Range	Formula
K2:L11	K2	=FILTER(G2:H100,YEAR(G2:G100)=J2)
Dynamic array formulas.

That's great!

 

[charlesstricklin]

I'm having trouble blending it into my real sheet, but you've shown me a direction to do. Much obliged.

 

[AhoyNC]

This link may be of help.