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]])

 

[JenniferMurphy]

Jennifer, here is the help you need to get started

https://www.amazon.com/Master-Your-Data-Excel-Power/dp/1615470581/ref=sr_1_1?dib=eyJ2IjoiMSJ9.koCXAoXSgRJt4F-5xsT8s_MYf7wZEeRvjHPBSleNE4-gJuTKTk3PSlkSdz0YMCwqUOkbN8ioDtsV2fcg5INoxg.akahv4X1_QtpeuMfPYANrmx6jBFAi8jEiVL3AahqopI&dib_tag=se&qid=1709954989&refinements=p_27%3AKen+Puls&s=books&sr=1-1

Thanks. It's on my ToDo list.

 

[JenniferMurphy]

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.

Aha! Thanks for that clarification. I did misunderstand your point.

 

[JenniferMurphy]

Here is another option.

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

I am marking this as the solution. It is the best solution for me.

Thanks

 

[Alex Blakenburg]

Love how you always provide a wrapup.

 

[JenniferMurphy]

Love how you always provide a wrapup.

I do it mainly for myself I often refer back to these threads. I hope it is helpful to others as well.