#### 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
edited 12/09/19

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:

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

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

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