I need a formula, related to date
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
-
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!
-
Hi @bisaacs ,
Thanks for your response. Here is the screenshot. If this " Due date" column matches the "Date"column in the reference sheet, then
- 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.
- 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.
-
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:
- =COUNTIFS(Done:Done, 1, Date:Date, [Due Date]@row)
- =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!
-
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)
-
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!
-
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 ?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!