# How to count how many are in the last 7 days

Options

I am trying to count how many cells are in the last 7 days

The formula I used is:

=COUNTIF([Avature Application]:[Avature Appication], AND(@cell<= TODAY(), @cell > TODAY(-7)))

Tags:

• ✭✭✭✭✭✭
Options

COUNTIF is intended to be used when you want to count cells that meet a single criterion. In your case, you are attempting to match two criteria. For this use case, you will want to use COUNTIFS.

=COUNTIFS([Avature Application]:[Avature Application], @cell <= TODAY(), [Avature Application]:[Avature Application], @cell > TODAY(-7))

• Options

Thank you this worked.

I have another question.

I want to count how many have been hired A220 in the below year to date

The formula I used:

=COUNTIFS([Date of CJO]:[Date of CJO], @cell <= TODAY(), [Date of CJO]:[Date of CJO], @cell > TODAY(-157), Fleet:Fleet, "A220")

and

=COUNTIFS([Interview Date]:[Interview Date], @cell <= TODAY(), [Interview Date]:[Interview Date], @cell > TODAY(-157), Fleet:Fleet, "A220")

• ✭✭✭✭✭✭
Options

I didn't set up a test sheet to test this, but it may give you what you are looking for.

=COUNTIFS([Fleet:Fleet], CONTAINS("A220", @cell), [Date of CJO]:[Date of CJO], YEAR(@cell) = YEAR(TODAY())

CONTAINS will find cells that have "A220" anywhere within. From your screenshot, it appears other text could possibly appear in a cell with A220, so this should account for that. Comparing the year of the CJO and TODAY means that you will not need to worry about how many days you are into the year, or even which year it is. When the calendar clicks over to 2024, it will begin looking for 2024 hirings.

For the interviews formula, just replace that column in the above formula. Let me know if this doesn't work for you.

• Options

Thank you unfortunately this didn't work it gave me an error code. the #Unparseable

• ✭✭✭✭✭✭
Options

Oops.... had a typo with some square brackets.

=COUNTIFS([Fleet]:[Fleet], CONTAINS("A220", @cell), [Date of CJO]:[Date of CJO], YEAR(@cell) = YEAR(TODAY())

• Options

Now it gave me #Invalid Data type

• ✭✭✭✭✭✭
Options

=COUNTIFS([Fleet]:[Fleet], CONTAINS("A220", @cell), [Date of CJO]:[Date of CJO], IFERROR(YEAR(@cell), 0) = YEAR(TODAY())

I suspect one or more of the dates is not formatted correctly, or maybe you have a blank date cell. The IFERROR will allow the formula to continue anyway if that is the issue.

• Options

This time it gave a value but the value is 0

• Options

It worked but this time it gave me 0

• Options

Dates are in the format of 06/7/23

Could that be causing the issue?

• Options

Wait I apologize the 0 was actually correct.

• ✭✭✭✭✭✭
Options

Are the date columns formatted as dates under the Column Properties? That would cause an issue if not.

• ✭✭✭✭✭✭
Options

You may want to throw in some temp data that you know should evaluate to greater than 0 just to be sure it is working.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!