Countifs Referencing Multiple Ranges in Another Sheet for Name and Date Range
I am trying to pull the total number for a specific person and a specific date range within a different sheet but also different ranges. I am able to get it to work with just the name or just the date range but gives an error of Incorrect Argument when I try to combine.
Formula I am using:
=COUNTIFS({PM and IC Assignments Range 16}, "Name", {Ready PM and IC Assignments Range 1}, >=DATE(2024, 1, 1))
I need to use a date range though for Jan, Feb, March, etc. So also need to add a <=DATE(202,1,31)
I've tried the following formula as well still receiving incorrect argument:
=COUNTIFS({Ready PM and IC Assignments Range 2}, >=DATE(2024, 1, 1), {Ready PM and IC Assignments Range 2}, <=DATE(2024, 1, 31), {PM and IC Assignments Range 16},"Name")
Best Answer
-
All ranges need to be of the same size/shape. So if one range within the function is a single column (date range) then all ranges within the function must be a single column.
You could add a helper column in the source sheet that combines the two name columns together and use a CONTAINS or HAS function (depending on how exactly you set up the helper column) in your COUNTIFS.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Smartsheet does not have BETWEEN, but there are workarounds. Some ideas:
-
Thank you James. Can I combine COUNTIFS and IF in the same argument though?
-
Also I am able to make the dates work with the following formula but error comes in when I try to add the name criteria.
=COUNTIFS({Ready PM and IC Assignments Range 2}, >=DATE(2024, 1, 1), {Ready PM and IC Assignments Range 2}, <=DATE(2024, 1, 31)
-
Hi @Megan Noell
Can you post the formula that you're trying to use, looking for the name? You should be able to add this in by using another {range} and "criteria", like so:
=COUNTIFS({Ready PM and IC Assignments Range 2}, >=DATE(2024, 1, 1), {Ready PM and IC Assignments Range 2}, <=DATE(2024, 1, 31), {Name column in the same sheet}, "Name")
However since you said you were getting an error, can I confirm that your Date column and your Name column are both coming from the same sheet? When you're using {references} in a function(), anything within the parentheses will need to be referencing the same, other sheet. You can't combine references within the same function, does that make sense?
For example:
=COUNTIFS({sheet 1}, "criteria", {sheet 1}, "criteria")
Now if your {Name} reference is on a different sheet, you won't be able to add that in since this COUNTIFs is only looking at Sheet 1.
If this isn't helping, can you post a screen capture of the sheet you're looking into, but block out sensitive data?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you Genevieve. Here is the formula I am trying to use. I need to reference more than one column for the name and am trying to use a different reference range but on the same sheet. The references are all coming from the same sheet, but different ranges.
=COUNTIFS({Ready PM and IC Assignments Range 2}, >=DATE(2024, 1, 1), {Ready PM and IC Assignments Range 2}, <=DATE(2024, 1, 31), {PM and IC Assignments Range 16}, "Name")
See screenshot of sheet referenced below. We are trying to get a module count (TLM, Accruals, Leave, etc columns) by name and date which is why a reference is needed on the name as well as the date.
-
All ranges need to be of the same size/shape. So if one range within the function is a single column (date range) then all ranges within the function must be a single column.
You could add a helper column in the source sheet that combines the two name columns together and use a CONTAINS or HAS function (depending on how exactly you set up the helper column) in your COUNTIFS.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Got it! Thanks for the idea. I'll give that shot!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 306 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!