I need a formula, related to date

Options

I need a formula, which wants to compare the date in the sheet I created, with the date in the reference sheet. If date is same, then first I need to count the total number of dates, which matches the date I provided. Then, Based on the date in another column I want to count the total number of tasks completed and not completed on other columns.

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @Roselin,

    I think it would be helpful if you could share screenshots of your sheets so that we can get a better picture of what data you're looking at, and how you want it returned. Please be sure to hide any sensitive information

    Otherwise, based on the first part of your description, the formula could be something like this:

    =IF([Date1] == [Date 2 reference sheet], COUNTIF([Date 2 reference column], [Date 1]))

    I'm not quite understanding the 2nd part, are you saying if the reference sheet date doesn't match the 1st date, then you want to count all the completed tasks that match that date, or are you looking for two separate formulas?

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Roselin
    Options

    Hi @bisaacs ,

    Thanks for your response. Here is the screenshot. If this " Due date" column matches the "Date"column in the reference sheet, then

    1. Total no.of task completed - It is based on the Done column, if it is checked then task is completed. I want the total number of tasks completed on that date.
    2. Total no.of task not completed - I want the remaining count of tasks not completed here (Which are basically the not checked ones) , based on the due date and Done column like above.

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @Roselin,

    Thanks for clarifying that! I think I was able to get a formula figured out. You'll want to make them column formulas so it populates for each row without having to drag the formula down:

    1. =COUNTIFS(Done:Done, 1, Date:Date, [Due Date]@row)
    2. =COUNTIFS(Done:Done, 0, Date:Date, [Due Date]@row)

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Roselin
    Options

    Hi @bisaacs ,

    I am getting "#UNPARSEABLE" error. Just to be clear the date column and done column are from a reference sheet. So, i am selecting the whole column while referencing it in formula. Don't know what went wrong.

    This is what I gave.

    =COUNTIFS({Copy of Test Range 1}:{Copy of Test Range 1}, 1, {Copy of Test Range 2}:{Copy of Test Range 2}, [Due Date]@row)

    Copy of Test Range 1 - Done column (from reference sheet)

    Copy of Test Range 2 - Date column (from reference sheet)

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @Roselin,

    When you use a sheet reference, you don't need to use the range ":" syntax to make it work, it would look like this:

    =COUNTIFS({Copy of Test Range 1}, 1, {Copy of Test Range 2}, [Due Date]@row)

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Roselin
    Options

    Hi @bisaacs

    That works! Thank you. I need a suggestion on one thing, can we pull the reference sheet data's directly from a report, I can't see them on the list of reference sheet. Is there a alternate way to get those data's pulled, as I need this data from 100+sheets ?

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @Roselin,

    Unfortunately I don't believe you can pull reference data from reports

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!