How to count how many are in the last 7 days
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)))
Answers
-
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))
-
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")
-
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.
-
Thank you unfortunately this didn't work it gave me an error code. the #Unparseable
-
Oops.... had a typo with some square brackets.
=COUNTIFS([Fleet]:[Fleet], CONTAINS("A220", @cell), [Date of CJO]:[Date of CJO], YEAR(@cell) = YEAR(TODAY())
-
Now it gave me #Invalid Data type
-
=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.
-
This time it gave a value but the value is 0
-
It worked but this time it gave me 0
-
Dates are in the format of 06/7/23
Could that be causing the issue?
-
Wait I apologize the 0 was actually correct.
-
Are the date columns formatted as dates under the Column Properties? That would cause an issue if not.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 431 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!