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

Options
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 ✓
    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.

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Options

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

    Return Value if Between Dates — Smartsheet Community

  • Megan Noell
    Options

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

  • Megan Noell
    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)

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

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



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    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.

  • Megan Noell
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!