How to count events in a calendar year?

[JenniferMurphy]

Suppose I have a log of lunches I have attended and I want to find out how many times I had lunch with each person overall and in each year. I was able to get the count of the total number of times, but my formula for the count in a year gets a Spill error.

Any suggestions? Is there a better way? Thanks

Lunch log test.xlsm
	B	C	D	E	F	G	H	I
3		Table Name: TblDates			Table Name: TblNames
4	Date	Name(s)	Location		Name	Total	2022	2023
5	9/15/22	Sleepy, Grumpy	Brew'd Awakening		Bugs Bunny	3	#SPILL!
6	8/01/22	Scarlet O'Hara, Henrietta Horne, Helen Bacque	Thai Me Up		Elbert Wonmug	2	#SPILL!
7	7/04/22	Henrietta Horne, Scarlet O'Hara	Lox, Stock, & Bagels		Fred Flintstone	1	#SPILL!
8	6/25/22	Helen Bacque, Rhett Butler	Wish You Were Beer		Grumpy	1	#SPILL!
9	6/16/22	Bugs Bunny	Garden of Eat'n		Helen Bacque	4	#SPILL!
10	6/05/22	Henrietta Horne, Helen Bacque	Dairy Godmother		Henrietta Horne	4	#SPILL!
11	6/03/22	Bugs Bunny	Lettuce Turnip the Beet		Neal Andertal	1	#SPILL!
12	5/19/22	Rhett Butler, Scarlet O'Hara, Pogo Possum	Cajun Delight		Pete Moss	1	#SPILL!
13	3/01/22	Helen Bacque	Hank's Hot Ribs		Pogo Possum	1	#SPILL!
14	1/15/22	Pete Moss, Bugs Bunny	Garden Center		Rhett Butler	2	#SPILL!
15	12/10/21	Scarlet O'Hara	Bread Zeppelin		Scarlet O'Hara	5	#SPILL!
16	8/25/21	Scarlet O'Hara	Aesop's Bagels		Sleepy	1	#SPILL!
17	6/13/21	Elbert Wonmug	Aesop's Tables
18	3/05/21	Neal Andertal, Fred Flintstone, Elbert Wonmug	Hard Rock Café
19	2/03/21	Henrietta Horne	Awful Annie's
Lunch Tallies

Cell Formulas
Range	Formula
G5:G16	G5	=COUNTIFS(TblDates[Name(s)],"*" & [@Name] & "*")
H5:H16	H5	=COUNTIFS(TblDates[Name(s)], "*" & [@Name] & "*", TblDates[Date], YEAR(TblDates[Date])=TblNames[[#Headers],[2022]])

 

[Jeffrey Mahoney]

Please try this

Book1
	B	C	D	E	F	G	H	I
3		Table Name: TblDates			Table Name: TblNames
4	Date	Name(s)	Location		Name	Total	2022	2023
5	9/15/2022	Sleepy, Grumpy	Brew'd Awakening		Bugs Bunny	4	3	1
6	8/1/2022	Scarlet O'Hara, Henrietta Horne, Helen Bacque	Thai Me Up		Elbert Wonmug	2	0	0
7	7/4/2022	Henrietta Horne, Scarlet O'Hara	Lox, Stock, & Bagels		Fred Flintstone	1	0	0
8	6/25/2022	Helen Bacque, Rhett Butler	Wish You Were Beer		Grumpy	1	1	0
9	6/16/2022	Bugs Bunny	Garden of Eat'n		Helen Bacque	5	4	1
10	6/5/2022	Henrietta Horne, Helen Bacque	Dairy Godmother		Henrietta Horne	4	3	0
11	6/3/2022	Bugs Bunny	Lettuce Turnip the Beet		Neal Andertal	1	0	0
12	5/19/2022	Rhett Butler, Scarlet O'Hara, Pogo Possum	Cajun Delight		Pete Moss	1	1	0
13	3/1/2022	Helen Bacque	Hank's Hot Ribs		Pogo Possum	1	1	0
14	1/15/2022	Pete Moss, Bugs Bunny	Garden Center		Rhett Butler	2	2	0
15	12/10/2021	Scarlet O'Hara	Bread Zeppelin		Scarlet O'Hara	5	3	0
16	8/25/2021	Scarlet O'Hara	Aesop's Bagels		Sleepy	1	1	0
17	6/13/2021	Elbert Wonmug	Aesop's Tables
18	3/5/2021	Neal Andertal, Fred Flintstone, Elbert Wonmug	Hard Rock Café
19	2/3/2021	Henrietta Horne	Awful Annie's
20	2/15/2023	Bugs Bunny	Doods Deluge
21	3/23/2023	Helen Bacque	Dans Dojo
Sheet1

Cell Formulas
Range	Formula
G5:G16	G5	=COUNTIFS(TblDates[Name(s)],"*" & [@Name] & "*")
H5:H16	H5	=SUM(COUNTIFS(TblDates[Name(s)], "*" & [@Name] & "*", TblDates[Date], ">="&DATE(TblNames[[#Headers],[2022]],1,1),TblDates[Date],"<="&DATE(TblNames[[#Headers],[2022]],12,31)))
I5:I16	I5	=SUM(COUNTIFS(TblDates[Name(s)], "*" & [@Name] & "*", TblDates[Date], ">="&DATE(TblNames[[#Headers],[2023]],1,1),TblDates[Date],"<="&DATE(TblNames[[#Headers],[2023]],12,31)))

 

[alansidman]

Jennifer here is an alternative with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Year = Table.TransformColumns(Source,{{"Date", Date.Year, Int64.Type}}),
    SplitColumnbyDelimiter = Table.ExpandListColumn(Table.TransformColumns(Year, {{"Name(s)", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Name(s)"),
    TrimmedText = Table.TransformColumns(SplitColumnbyDelimiter,{{"Name(s)", Text.Trim, type text}}),
    GroupedByYearAndGuest = Table.Group(TrimmedText, {"Date", "Name(s)"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    GroupedByYearAndGuest

Book3
	A	B	C	D	E	F	G
2	Date	Name(s)	Location		Date	Name(s)	Count
3	9/15/2022	Sleepy, Grumpy	Brew'd Awakening		2022	Sleepy	1
4	8/1/2022	Scarlet O'Hara, Henrietta Horne, Helen Bacque	Thai Me Up		2022	Grumpy	1
5	7/4/2022	Henrietta Horne, Scarlet O'Hara	Lox, Stock, & Bagels		2022	Scarlet O'Hara	4
6	6/25/2022	Helen Bacque, Rhett Butler	Wish You Were Beer		2022	Henrietta Horne	3
7	6/16/2022	Bugs Bunny	Garden of Eat'n		2022	Helen Bacque	4
8	6/5/2022	Henrietta Horne, Helen Bacque	Dairy Godmother		2022	Rhett Butler	2
9	6/3/2022	Bugs Bunny	Lettuce Turnip the Beet		2022	Bugs Bunny	3
10	5/19/2022	Rhett Butler, Scarlet O'Hara, Pogo Possum	Cajun Delight		2022	Pogo Possum	1
11	3/1/2022	Helen Bacque	Hank's Hot Ribs		2022	Pete Moss	1
12	1/15/2022	Pete Moss, Bugs Bunny	Garden Center		2021	Scarlet O'Hara	1
13	3/7/2022	Scarlet O'Hara	Bread Zeppelin		2021	Elbert Wonmug	2
14	8/25/2021	Scarlet O'Hara	Aesop's Bagels		2021	Neal Andertal	1
15	6/13/2021	Elbert Wonmug	Aesop's Tables		2021	Fred Flintstone	1
16	3/5/2021	Neal Andertal, Fred Flintstone, Elbert Wonmug	Hard Rock Café		2021	Henrietta Horne	1
17	2/3/2021	Henrietta Horne	Awful Annie's
Sheet2

 

[JenniferMurphy]

Please try this

That clearly works. Thanks. I guess CountIfs won't allow me to pass the criteria to a function for processing prior to making the comparison, like this:

=SUM(COUNTIFS(TblDates[Names], "*" & [@Name] & "*",   YEAR(TblDates[Date]), TblNames[[#Headers],[2022]]))

It seems like it should work as well as the last column here where I extracted the year to a separate column.

Lunch log test.xlsm
	B	C	D	E	F	G	H	I	J	K
3			Table Name: TblDates			Table Name: TblNames
4	Date	Year	Names	Location		Name	Total	2021	2022	2022-2
5	2/03/21	2021	Henrietta Horne	Awful Annie's		Bugs Bunny	3	0	3	3
6	3/05/21	2021	Neal Andertal, Fred Flintstone, Elbert Wonmug	Hard Rock Café		Elbert Wonmug	2	2	0	0
7	6/13/21	2021	Elbert Wonmug	Aesop's Tables		Fred Flintstone	1	1	0	0
8	8/25/21	2021	Scarlet O'Hara	Aesop's Bagels		Grumpy	1	0	1	1
9	12/10/21	2021	Scarlet O'Hara	Bread Zeppelin		Helen Bacque	4	0	4	4
10	1/15/22	2022	Pete Moss, Bugs Bunny	Garden Center		Henrietta Horne	4	1	3	3
11	3/01/22	2022	Helen Bacque	Hank's Hot Ribs		Neal Andertal	1	1	0	0
12	5/19/22	2022	Rhett Butler, Scarlet O'Hara, Pogo Possum	Cajun Delight		Pete Moss	1	0	1	1
13	6/03/22	2022	Bugs Bunny	Lettuce Turnip the Beet		Pogo Possum	1	0	1	1
14	6/05/22	2022	Henrietta Horne, Helen Bacque	Dairy Godmother		Rhett Butler	2	0	2	2
15	6/16/22	2022	Bugs Bunny	Garden of Eat'n		Scarlet O'Hara	5	2	3	3
16	6/25/22	2022	Helen Bacque, Rhett Butler	Wish You Were Beer		Sleepy	1	0	1	1
17	7/04/22	2022	Henrietta Horne, Scarlet O'Hara	Lox, Stock, & Bagels
18	8/01/22	2022	Scarlet O'Hara, Henrietta Horne, Helen Bacque	Thai Me Up
19	9/15/22	2022	Sleepy, Grumpy	Brew'd Awakening
Lunch Tallies

Cell Formulas
Range	Formula
H5:H16	H5	=COUNTIFS(TblDates[Names],"*" & [@Name] & "*")
I5:I16	I5	=SUM(COUNTIFS(TblDates[Names], "*" & [@Name] & "*", TblDates[Date], ">="&DATE(TblNames[[#Headers],[2021]],1,1), TblDates[Date],"<="&DATE(TblNames[[#Headers],[2021]],12,31)))
J5:J16	J5	=SUM(COUNTIFS(TblDates[Names], "*" & [@Name] & "*", TblDates[Date], ">="&DATE(TblNames[[#Headers],[2022]],1,1), TblDates[Date],"<="&DATE(TblNames[[#Headers],[2022]],12,31)))
K5:K16	K5	=COUNTIFS(TblDates[Names], "*" & [@Name] & "*", TblDates[Year], "="&TblNames[[#Headers],[2022]])
C5:C19	C5	=YEAR([@Date])

 

[JenniferMurphy]

Jennifer here is an alternative with Power Query

I have been meaning to get up to speed with Power Query. Maybe this will be the motivation I need to actually do it!

Thanks

 

[Alex Blakenburg]

I guess CountIfs won't allow me to pass the criteria to a function for processing prior to making the comparison,

Unfortunately Countifs (incl the other varitions ie Sumifs) spefically require a Range in the Criteria Lookup position and won't accept an array.
Putting a function around the range converts it to an array.
=countifs( criteria_range, criteria,...)

I am not sure you get much mileage out of using the Structured referencing for getting the value from the heading.
Here is another option.

=SUM(FILTER(--ISNUMBER(TblDates[Date]), (YEAR(TblDates[Date])=VALUE(H$4)) * (ISNUMBER(SEARCH([@Name],TblDates[Name(s)]))) ,0) )

 

[JenniferMurphy]

Unfortunately Countifs (incl the other varitions ie Sumifs) spefically require a Range in the Criteria Lookup position and won't accept an array.
Putting a function around the range converts it to an array.
=countifs( criteria_range, criteria,...)

I am not sure you get much mileage out of using the Structured referencing for getting the value from the heading.
Here is another option.

=SUM(FILTER(--ISNUMBER(TblDates[Date]), (YEAR(TblDates[Date])=VALUE(H$4)) * (ISNUMBER(SEARCH([@Name],TblDates[Name(s)]))) ,0) )

Cool. Another very useful function that I was not aware of. Thanks

Why do you say that it is better (mileage?) than the other (structured) solution. Isn't a "structured" reference simply a table reference? And aren't you using table references in your solution?

 

[JenniferMurphy]

I now have three solutions.

1. The 2021 column uses the Jeffrey Mahoney solution.
2. The 2021-2 column uses the Alex Blankenburg solution.
3. The 2021-3 column makes use of a helper column ([Year]).

Lunch log test.xlsm
	B	C	D	E	F	G	H	I	J	K	L
3			Table Name: TblDates			Table Name: TblNames
4	Date	Year	Names	Location		Name	Total1	Total2	2021	2021-2	2021-3
5	2/03/21	2021	Henrietta Horne	Awful Annie's		Bugs Bunny	3	3	0	0	0
6	3/05/21	2021	Neal Andertal, Fred Flintstone, Elbert Wonmug	Hard Rock Café		Elbert Wonmug	2	2	2	2	2
7	6/13/21	2021	Elbert Wonmug	Aesop's Tables		Fred Flintstone	1	1	1	1	1
8	8/25/21	2021	Scarlet O'Hara	Aesop's Bagels		Grumpy	1	1	0	0	0
9	12/10/21	2021	Scarlet O'Hara	Bread Zeppelin		Helen Bacque	4	4	0	0	0
10	1/15/22	2022	Pete Moss, Bugs Bunny	Garden Center		Henrietta Horne	4	4	1	1	1
11	3/01/22	2022	Helen Bacque	Hank's Hot Ribs		Neal Andertal	1	1	1	1	1
12	5/19/22	2022	Rhett Butler, Scarlet O'Hara, Pogo Possum	Cajun Delight		Pete Moss	1	1	0	0	0
13	6/03/22	2022	Bugs Bunny	Lettuce Turnip the Beet		Pogo Possum	1	1	0	0	0
14	6/05/22	2022	Henrietta Horne, Helen Bacque	Dairy Godmother		Rhett Butler	2	2	0	0	0
15	6/16/22	2022	Bugs Bunny	Garden of Eat'n		Scarlet O'Hara	5	5	2	2	2
16	6/25/22	2022	Helen Bacque, Rhett Butler	Wish You Were Beer		Sleepy	1	1	0	0	0
17	7/04/22	2022	Henrietta Horne, Scarlet O'Hara	Lox, Stock, & Bagels
18	8/01/22	2022	Scarlet O'Hara, Henrietta Horne, Helen Bacque	Thai Me Up
19	9/15/22	2022	Sleepy, Grumpy	Brew'd Awakening
Lunch Tallies

Cell Formulas
Range	Formula
H5:H16	H5	=COUNTIFS(TblDates[Names],"*" & [@Name] & "*")
I5:I16	I5	=COUNT(SEARCH([@Name],TblDates[Names]))
J5:J16	J5	=SUM(COUNTIFS(TblDates[Names], "*" & [@Name] & "*", TblDates[Date], ">="&DATE(TblNames[[#Headers],[2021]],1,1), TblDates[Date],"<="&DATE(TblNames[[#Headers],[2021]],12,31)))
K5:K16	K5	=SUM(FILTER(--ISNUMBER(TblDates[Date]), (YEAR(TblDates[Date])=VALUE(TblNames[[#Headers],[2021]])) * (ISNUMBER(SEARCH([@Name],TblDates[Names]))),0) )
L5:L16	L5	=COUNTIFS(TblDates[Names], "*" & [@Name] & "*", TblDates[Year], "="&TblNames[[#Headers],[2021]])
C5:C19	C5	=YEAR([@Date])

 

[alansidman]

Jennifer, here is the help you need to get started

Amazon.com

 

[Alex Blakenburg]

Why do you say that it is better (mileage?) than the other (structured) solution. Isn't a "structured" reference simply a table reference? And aren't you using table references in your solution?

You misread my comment and in doing so changed my suggested formula in your summarised version.
I was questioning whether there is any harm in deviating from using structured referencing when it doesn't suit ?
I did not use
VALUE(TblNames[[#Headers],[2021]]))
per your summarised version, in my suggestion since that structured reference hard codes the heading column to 2021 (absolute referencing it) so that when you copy it to the next column it doesn't change to 2022, then 2023 in the next etc.

I used
VALUE(H$4)
Only locking in the row but leaving the column as relative so that it could easily be copied across.