COUNTIFS with Multiple Criteria and Dates
I am trying to figure out how many of the cells contain certain practice locations on a certain date. For instance I was to know the amount of orders from BWMG Adult and Senior Care, BWMG Hanover, BWMG Pasadena, BWMG Primary Care North, BWMG Shipley, on 01/05/2024, I have tried various formats for the formula and cannot get it to work, this is what I have currently, however it is not returning the correct number.
=COUNTIFS({THMD CareKit Referral Form_Master Range 1}, "BWMG Adult and Senior Care") + COUNTIFS({THMD CareKit Referral Form_Master Range 1}, "BWMG Hanover") + COUNTIFS({THMD CareKit Referral Form_Master Range 1}, "BWMG Pasadena") + COUNTIFS({THMD CareKit Referral Form_Master Range 1}, "BWMG Primary Care North") + COUNTIFS({THMD CareKit Referral Form_Master Range 1}, "BWMG Shipley") + (COUNTIFS({THMD CareKit Referral Form_Master Range 2}, "2024, 01, 05"))
Source Sheet:
Data Sheet, should be returning 1 for each cell but the numbers aren't correct...
Answers
-
Try this instead:
=COUNTIFS({Location Column}, OR(@cell = "Location A", @cell = "Location B", @cell = "Location C"), {Date Column}, @cell = DATE(2024, 01, 05))
-
@Paul Newcome That worked thank you so much!
I am now not able to get it to combine two countif statements when there is only one location in the criteria it needs to look for, what do I need to correct?
I am trying to see how many were ordered on 01/12/2024 for Family Medical Associates
-
You don't use an AND with two COUNTIF functions. You use a COUNTIFS like I did in my last comment.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!