Need help on countifs formula

Hi to everyone!

Appreciate to help me out to figure out why below formula is incorrect arguement?

=COUNTIFS({OPEN PR list Range 9}, [Primary Column]@row, {OPEN PR list Range 13}, <=DATE(2024, 4, 1), {OPEN PR list Range 13}, >=DATE(2024, 4, 30))

If i use no date range it works.

This is how the data file looks like

I wanted to count how many the purchaser has done based on the date range which monthly.

Thank you and awaiting anxiously.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/08/24

    @RobertZapata

    After looking at your formula you have the < and > in the wrong locations. The way you have it you are looking for a date that's less then 2004,4,1 and also greater then 2004,4,30. There is no date that would exist in that range. Try.

    =COUNTIFS({OPEN PR list Range 9}, [Primary Column]@row, {OPEN PR list Range 13}, >=DATE(2024, 4, 1), {OPEN PR list Range 13}, <=DATE(2024, 4, 30))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Itai
    Itai ✭✭✭✭✭✭

    Hi @RobertZapata

    Did you try leaving only the date arguments to see if it works?

    Are all the ranges the same size? They all use a full column?

    I ran a test and it seems to work for me but I am probably missing something.

    Itai Perez

    Reporting and Project Manager

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

  • Hi @Itai,

    Thank you for the comment but i did it as you can see below it works but when i add purchaser name it is telling me the incorrect arguement?

    I am really confused why i could not have these 2 arguements to work together.

  • Hi @RobertZapata

    Can you double check that both of your {ranges} are in the same sheet and the same length?

    https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--incorrect-argument-set

    This error message can appear if your ranges are different lengths. I would suggest edit each reference and click the column name so all rows are selected. Let us know if that works for you!

    Cheers,
    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Hi @Genevieve P. ,

    Yes both ranges are in one reference sheet.

  • Hi @RobertZapata

    It's good that they're on the same sheet, but can you confirm they're the same length?

    For example, if {list purchaser 9} is referencing rows 1-5, but the {Date} reference is from rows 1-10, then you'll get an error. They have to have an equal number of rows in each reference.

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!