CountIf wildcard and dates

PatRichard

New Member
Joined
Dec 29, 2018
Messages
12
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.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
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
YZAAABACADAEAFAG
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
Joined
Apr 29, 2016
Messages
320
Try using the sumproduct function:

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

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top