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

Megan Noell
Megan Noell ✭✭
edited 11/03/23 in Formulas and Functions

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭

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

    Return Value if Between Dates — Smartsheet Community

  • 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.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!