Matching a DATE in a Formula

Options
Elizabeth Aird
Elizabeth Aird ✭✭✭✭
edited 09/10/20 in Formulas and Functions

I am trying to streamline this a bit more. I have 14 plants and continual current dates to update. So there has to be an easier way.


I want to find the plant AND match the date from the Date column for the Countifs formula.

=COUNTIFS({StencilDate}, IFERROR(DATE(@CELL),0)=Date3, {Plant}, "Northgate"))

OR

=COUNTIFS({Plant}:{Plant}, "Northgate", {StencilDate}:{StencilDate}, IFERROR(DATE(@CELL), 0)=Date2))


But it's not working. Appreciate your help.

Best Answer

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓
    Options

    =COUNTIFS({StencilDate}, Date3, {Plant}, "Northgate")

    Give that a try

  • Elizabeth Aird
    Elizabeth Aird ✭✭✭✭
    Options

    Bless you!!! I was really trying to make it too complicated!

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    NP. A couple notes about what I think happened with your formula and how to use the parts you used incorrectly are below:

    @cell

    @cell is a very useful tool, but generally you only use it if you are using another formula to modify the value, or are checking multiple values on the same range. An example of correct use is below.

    =countif(a:a,or(@cell = 1, @cell = 2))

    The formula above will count all values of column a where the cell is equal to 1 or 2.

    iferror()

    The iferror is used in a lot of date formulas as MONTH DAY and YEAR will pop an error if they are used on a blank cell. An example of correct use is below

    =countif(date:date,iferror(month(@cell),0) = 1)

    The formula above will count all dates in column date where the month is january

    date()

    Date is used to convert text values into the date format so you can use other date related formula to analyze. If your data is already in date format, and in a date column, you don't need to use this formula. If you do utilize the date formula, you need to put in a year, month, and day individually for it to perform. An example of correct use is below

    =date(year(today()),month(today()),1)

    The formula above will return the first day of the current month

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!