# CountIf wildcard and dates

#### PatRichard

##### New Member
Greetings!
I have a countifs that works perfectly:
Excel Formula:
``=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?

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

##### Well-known Member
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
55ItemPurchasedQuantityPurchasedTextPurchasedYear
562020Rice1/1/2020102020/01/012020
57Beans2/2/2019222019/02/022019
58Current3Carrots1/1/2020332020/01/012020
59Wildcard3Parsnips2/2/2019442019/02/022019
60Just Year3Leeks6/6/2020552020/06/062020
61Pumpkins7/7/2021662021/07/072021
Sheet1
Cell Formulas
RangeFormula
AF56:AF61AF56=TEXT([@Purchased],"yyyy/mm/dd")
AG56:AG61AG56=YEAR([@Purchased])
Z58Z58=COUNTIFS(MyInventory[Purchased],">="&DATE(Z56,1,1),MyInventory[Purchased],"<="&DATE(Z56,12,31))
Z59Z59=COUNTIFS(MyInventory[PurchasedText],Z56&"*")
Z60Z60=COUNTIFS(MyInventory[PurchasedYear],Z56)

#### Phuoc

##### Active Member
Try using the sumproduct function:

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

Replies
1
Views
325
Replies
6
Views
250
Replies
7
Views
308
Replies
7
Views
173
Replies
4
Views
196

1,127,610
Messages
5,625,814
Members
416,138
Latest member
Pizzaman22

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back