# Countifs Referencing Multiple Ranges in Another Sheet for Name and Date Range

Options
edited 11/03/23

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")

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

Smartsheet does not have BETWEEN, but there are workarounds. Some ideas:

Return Value if Between Dates — Smartsheet Community

• Options

Thank you James. Can I combine COUNTIFS and IF in the same argument though?

• Options

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)

Options

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?

• Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

Got it! Thanks for the idea. I'll give that shot!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!