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

Megan Noell

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

Paul Newcome

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.

James Keuning

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

Return Value if Between Dates — Smartsheet Community

Megan Noell

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

Megan Noell

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)

Genevieve P.

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?

Megan Noell

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.

Paul Newcome

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.

Megan Noell

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

Paul Newcome

Happy to help. 👍️