=IF([End Date]@row,>=TODAY,"Yes", "no")

Sam Walsh

Trying to tabulate in process Items.. I want it to toggle YES if the end date is past today and No if the end date has not been reached--- from there I want to sum the "Yes" values

I have to break this up by quarter so also was going to start with each item by date then add an additional for Quarter so... If Q1 and Date is after today "yes" if Q1 and before today "no".

Thank you in advance!

AnalyticOwl

Hi Sam,

If you change your initial IF formula to:

=IF([End Date]@row >= TODAY(), "Yes", "no") will do what you want, and then count them you can do:

=COUNTIF([name of column]:[name of column], "Yes") using the name of the column as the name where you put the formula above.

About the quarters you can set up an additional column with a formula like:

=IF(ISDATE([End date]@row), IF(MONTH([End date]@row) <= 3, "Q1", IF(MONTH([End date]@row) <= 6, "Q2", IF(MONTH([End date]@row) <= 9, "Q3", "Q4"))))

You can do a combine COUNTIFS including both conditions but I am not sure what exactly you want to count on that side but hope this could give you a good start!

Good vibes

Jhair

Jeff Reisman

You can use the AND function within IF to designate multiple criteria that must be true. The syntax is

IF(AND(logical expression 1, logical expression 2, ...), value if true, value if false)

In this case, I'm nesting a second IF because presumably you don't want to set the value for anything not in Q1.

=IF(AND(Quarter@row = "Q1", [End Date]@row >= TODAY()), "Yes", IF(AND(Quarter@row = "Q1", [End Date]@row < TODAY()), "No"))

To count the Yes values, use COUNTIFS. You don't need AND here because it is included in the COUNTIFS function.

The syntax for COUNTIFS is:

COUNTIFS(range 1, criteria 1, range 2, criteria 2, ...)

=COUNTIFS(Quarter:Quarter, "Q1", Status:Status, "Yes")