# 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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

##### 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
357
Replies
6
Views
290
Replies
7
Views
332
Replies
7
Views
197
Replies
4
Views
198

1,129,674
Messages
5,637,731
Members
416,981
Latest member
PLonchar

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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