I need a formula, related to date

Joyjenz

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.

bisaacs

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?

Joyjenz

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

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!

Joyjenz

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

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!

Joyjenz

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

Hey @Roselin,

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