Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Nesting COUNTIF/AND Formula

Options
Ray Rios
Ray Rios
edited 12/09/19 in Archived 2017 Posts

I am trying to create a formula that returns the count of items past due, 0-30 days, 31-60 days, and greater than 61 days.

 

The formulas I'm using is (respectively):

=COUNTIF(AND([Past Due]2:[Past Due]18, "True", [# of Days Past Due]2:[# of Days Past Due]18, <31 >= 0))
=COUNTIF(AND([Past Due]2:[Past Due]18, "True", [# of Days Past Due]2:[# of Days Past Due]18, >=31 <= 60))
=COUNTIF(AND([Past Due]2:[Past Due]18, "True", [# of Days Past Due]2:[# of Days Past Due]18, >=61))

 

 

however, I keep getting the following error "INCORRECT ARGUMENT SET".

Tags:

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 03/20/17
    Options

    Ray,

     

    Is the  [Past Due] column a check box or text?

     

    Why do you need a [Past Due] column if you are determining how many days past due it is?

     

    I would write the 0-30 day formula like this:

     

    =COUNTIFS([# of Days Past Due]2:[# of Days Past Due]18, >=0, [# of Days Past Due]2:[# of Days Past Due]18, <31)

     

    The COUNTIFS function acts the same as 

     

    COUNTIF(AND(expression1, criteria1, expression2, criteria2))

     

    For >-61, you can revert to COUNTIF because there is only the one criteria.

     

    Hope this helps.

     

    Craig

     

     

     

     

  • Ray Rios
    Options

    Hi Craig,

     

    Yes, the [Past Due] column is a check box. I also have a column that counts the [# of Days Past Due]. I have both of these because I have some conditional formatting running in the background and thought I could leverage the information to run formulas as well.

     

    The =COUNTIFS function you provided worked out. Thanks!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Ray,

     

    I thought that might be the case.

    As you can see, it was not needed for the functionality you asked for.

     

    In the future, be aware the Check Box columns are  Boolean.

     

    these will all get the same results. I prefer the first one.

    =IF(ChkBox23, "I am true", "I am false")

    =IF(ChkBox23 =  1, "I am true", "I am false")

    =IF(ChkBox23 = true, "I am true", "I am false")

    =IF(ChkBox23 = "true", "I am true", "I am false")

    =IF(ChkBox23 = "True", "I am true", "I am false")

     

    I was surprised the last two work, but they do.

     

    Craig

     

     

This discussion has been closed.