Matching a Date from Origin to Destination Sheet

Options

I am running a countif formula to count the number of certain events each day. The origin sheet has the "Event" column and a "Date" column that are feeding the count to the destination sheet. The destination sheet had a "Date" column and individual columns that contain the formula showing the counts per day of each event. Below is the formula I'm currently using:


=COUNTIFS({Event Report Form Range 1}, HAS(@cell, "Decision to Recover"), {Event Report Form Range 4}, HAS(@cell, "04/12/24"))


My question is: Is there a way to write the formula so that the formula matches the "Date" in the row of the destination sheet to the "Date" in the origin sheet so I can convert this to a column formula and not have to change the date in every cell?

Thanks in advance

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer βœ“
    Options

    Hi @andyhob

    Is your Β {Event Report Form Range 4} referencing a date type of column, or a text column with dates listed as text?

    If it's text, unfortunately you will need to search for the text by using quotes as you have there, and change it for each row/date.

    However if your Range 4 column is a Date column then yes! You can simply reference a Date cell in your current sheet to look for that value:

    =COUNTIFS({Event Report Form Range 1}, HAS(@cell, "Decision to Recover"), {Event Report Form Range 4}, [Date Reference]@row))

    Let me know if this makes sense and works for you.

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer βœ“
    Options

    Hi @andyhob

    Try and INDEX(MATCH combination! πŸ™‚

    We'll INDEX the column you want to return, using the MATCHING value of the week number. Something like this:

    =INDEX({Decision to Recover Column}, MATCH(Week@row, {Week Column}, 0))

    You could also likely use a SUMIFS, if you would have duplicate week rows in your source sheet:

    =SUMIFS({Decision to Recover Column}, {Week Column}, Week@row)

    Let me know if either of these work for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer βœ“
    Options

    Hi @andyhob

    Is your Β {Event Report Form Range 4} referencing a date type of column, or a text column with dates listed as text?

    If it's text, unfortunately you will need to search for the text by using quotes as you have there, and change it for each row/date.

    However if your Range 4 column is a Date column then yes! You can simply reference a Date cell in your current sheet to look for that value:

    =COUNTIFS({Event Report Form Range 1}, HAS(@cell, "Decision to Recover"), {Event Report Form Range 4}, [Date Reference]@row))

    Let me know if this makes sense and works for you.

    Cheers,

    Genevieve

  • andyhob
    andyhob ✭✭
    Options

    Thanks, I don't why that did come to mind. It worked perfect.

  • andyhob
    andyhob ✭✭
    Options

    On a similar note. If I am using WEEKNUMBER(TODAY(+1)) in one sheet to keep an updated count for the week; does the column in my origin sheet need to also use this formula, or will in recognize a manually input numerical value?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    I'm glad that worked for you! πŸ™‚

    You can match an output from your WEEKNUMBER function to a manually input numerical value, yes!

  • andyhob
    andyhob ✭✭
    Options

    Ok, well I guess I need help with that one too.

    I have an origin sheet that I am trying to pull the contents of "Decision to Recover" based on the "Week"

    In the destination sheet the "Week" column has formula =WEEKNUMBER(TODAY(+1)) to roll over at each new week.

    I am trying to pull the value from "Decision to Recover" the corresponds to the "Week" value from the origin sheet that matches the "Week" value in the destination sheet.

    I have tried multiple variations of COUNTIF formulas and get different error messages and sometimes "0" when there should be a value.

    Your help is very much appreciated.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer βœ“
    Options

    Hi @andyhob

    Try and INDEX(MATCH combination! πŸ™‚

    We'll INDEX the column you want to return, using the MATCHING value of the week number. Something like this:

    =INDEX({Decision to Recover Column}, MATCH(Week@row, {Week Column}, 0))

    You could also likely use a SUMIFS, if you would have duplicate week rows in your source sheet:

    =SUMIFS({Decision to Recover Column}, {Week Column}, Week@row)

    Let me know if either of these work for you!

    Cheers,

    Genevieve

  • andyhob
    andyhob ✭✭
    Options

    Your INDEX formula worked perfectly!! Thanks again for the help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!