=COUNTIFS({M&A Program Interest}, CONTAINS("Buyer Consult", @cell), {M&A Intake Form Range 1}, @cell > YEAR

Trying to utilize this formula; pull metrics from the past year only.


  • Alpha Chucky
    Alpha Chucky ✭✭✭✭

    I responded to your other post.

    Here's the formula I provided:

    =IF(AND(Year@row = 2022, [Range 1]@row = "Buyer Consult"), "Match", "No Match")

    Projects Delivered. Data Defended.

  • tmagana
    tmagana ✭✭

    Not quite.

    =COUNTIFS({M&A Program Interest}, CONTAINS("Buyer Consult", @cell), {M&A Intake Form Range 1}, @cell > TODAY(-268))

    This is my current formula. Is there an easier way than changing the subtract amount everyday. I am sure there is

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @tmagana,

    You can skip having a year column if you're just looking at previous year only:

    =COUNTIFS({Date range}, IFERROR(YEAR(@cell), 0) = (YEAR(TODAY()) - 1), {M&A Program Interest}, CONTAINS("Buyer Consult", @cell))

    The cross sheet references would be pointing that the relevant columns:

    If you wanted by fixed year rather than rolling, you would change the (YEAR(TODAY() -1) to the relevant year (e.g. 2022). Equally you can increase the number in this to look further back (e.g. compare with 2 years ago).

    If your M&A Program Interest column is a single select drop down rather than needing to look for the phrase you can also drop the CONTAINS("Buyer Consult",@cell) and just use "Buyer Consult" instead.

    Hope this helps somewhat, but if you've problems/questions then just post!

  • tmagana
    tmagana ✭✭

    =COUNTIF({M&A Intake Form}, IFERROR(YEAR(@cell), 0 = (YEAR(TODAY())-1),{M&A Program Interest}, CONTAINS("Buyer Consult", @cell)))))

    Am I inputting it wrong?

    Here are my columns

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @tmagana

    Just jumping in here because it looks like all you're missing is a closing parentheses after the first YEAR function!

    =COUNTIF({M&A Intake Form}, IFERROR(YEAR(@cell), 0) = (YEAR(TODAY())-1),{M&A Program Interest}, CONTAINS("Buyer Consult", @cell))



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!