Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Using COUNTIF statements

Topic Labels: Formulas
359 3
cancel
Showing results for 
Search instead for 
Did you mean: 
user9658457988
4 - Data Explorer
4 - Data Explorer

Hi all,

I need to generate a 4 digit code from the year a record was created and how many records have come before in that year.

E.g the 5th record that has been created in 2023 = 2305.

I have reformatted the referral date 

DATETIME_FORMAT({Date of Referral},"YY") but unable to get a COUNTIF function to work to count how many referrals have been made before this one where the referral date is in the same year.
3 Replies 3
bernie654
4 - Data Explorer
4 - Data Explorer

@user9658457988 wrote:

Hi all,

I need to generate a 4 digit code from the year a record was created and how many records have come before in that year.

E.g the 5th record that has been created in 2023 = 2305.

I have reformatted the referral date 

DATETIME_FORMAT({Date of Referral},"YY") but unable to get a COUNTIF function to work to count how many referrals have been made before this one where the referral date is in the same year.

Here's the formula to generate the 4-digit code:

Excel
 
=TEXT(DATETIME_FORMAT({Date of Referral},"YY")&COUNTIFS({Date of Referral},">="&{Date of Referral},{Date of Referral},"<="&EOMONTH({Date of Referral},0)),"0000")

Explanation:

  1. DATETIME_FORMAT({Date of Referral},"YY"): This part extracts the year from the "Date of Referral" column.
  2. COUNTIFS({Date of Referral},">="&{Date of Referral},{Date of Referral},"<="&EOMONTH({Date of Referral},0)): This part counts the number of records within the same year as the current record.
    • ">="&{Date of Referral}: This condition ensures that only records on or after the current record's date are included in the count.
    • "<="&EOMONTH({Date of Referral},0): This condition ensures that only records within the same year as the current record are included in the count. EOMONTH({Date of Referral},0) returns the last day of the month for the given date.
  3. TEXT(..., "0000"): This part formats the concatenated string (year + count) as a 4-digit number with leading zeros.

Example:

If the "Date of Referral" for a record is "2023-07-15" and there are 5 records with referral dates in 2023 up to and including July 15th, the formula will generate the code "2305".

Note:

  • This formula assumes that your "Date of Referral" column is formatted as a date.
  • Adjust the cell references in the formula to match the actual location of your data.

This formula should effectively generate the 4-digit code you require.

Thanks for the explanation!

Air table doesn't support countifs, eomonth or text functions so this formula doesn't work. Do you have a work around for these functions?

AbigailAdams
4 - Data Explorer
4 - Data Explorer

Thank you so much for the help.

Spoiler
COUNTIF formulas can be tricky, just like structuring a strong research paper. While technical formulas require careful input, so do academic arguments. A reliable essay writing service at https://ukwritemyessay.com/ can help streamline complex ideas and ensure they’re organized logically. What’s your specific use case for the formula, and do you think a service like this could simplify things for you? ensures customer satisfaction by offering free revisions. If a student needs modifications, the UK Write My Essay service platform allows them to request changes until they are fully satisfied with the final work.