CountIf wildcard and dates

[PatRichard]

Greetings!
I have a countifs that works perfectly:

=COUNTIFS(MyInventory[Purchased],">="&DATE(Z56,1,1),MyInventory[Purchased],"<="&DATE(Z56,12,31))

Basically looks at the Purchased column for rows that match a certain field in another table that contains years. So Purchased might have 12/25/2020, and the Z56 cell might be 2020. So, we have a match. All is good. Just wondering if there is a more efficient way of writing the formula? Maybe convert to countif with a single criteria but using wildcards for month/day? I've tried the obvious choices of removing the second criteria, switching to = for the comparison, and using * for the month and day with no love. The official doc (COUNTIF function) doesn't seem to help.

Suggestions?

 

[Toadstool]

Hi PatRichard,

Excel holds dates as integer days since 1st January 1900 (or 1904 for Mac) so today 26 December 2020 is held as 44,191. This doesn't lend itself to a wildcard search.

You could add a column which hold the date as yyyy/mm/dd format and do a wildcard search on that, or even just add a column for the year:

PatRichard.xlsx
	Y	Z	AA	AB	AC	AD	AE	AF	AG
55					Item	Purchased	Quantity	PurchasedText	PurchasedYear
56		2020			Rice	1/1/2020	10	2020/01/01	2020
57					Beans	2/2/2019	22	2019/02/02	2019
58	Current	3			Carrots	1/1/2020	33	2020/01/01	2020
59	Wildcard	3			Parsnips	2/2/2019	44	2019/02/02	2019
60	Just Year	3			Leeks	6/6/2020	55	2020/06/06	2020
61					Pumpkins	7/7/2021	66	2021/07/07	2021
Sheet1

Cell Formulas
Range	Formula
AF56:AF61	AF56	=TEXT([@Purchased],"yyyy/mm/dd")
AG56:AG61	AG56	=YEAR([@Purchased])
Z58	Z58	=COUNTIFS(MyInventory[Purchased],">="&DATE(Z56,1,1),MyInventory[Purchased],"<="&DATE(Z56,12,31))
Z59	Z59	=COUNTIFS(MyInventory[PurchasedText],Z56&"*")
Z60	Z60	=COUNTIFS(MyInventory[PurchasedYear],Z56)

 

[Phuoc]

Try using the sumproduct function:

=SUMPRODUCT(--(YEAR(MyInventory[PurchasedYear])=Z56))