COUNTIF + LAST YEAR INFO

tmagana

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

Michael Drayton

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

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

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

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

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))

Cheers,

Genevieve