Nesting COUNTIF/AND Formula

Ray Rios

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

J. Craig Williams

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

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

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