Formula adding previous days

Andrea Westrich

My goal is to have a summary sheet that shows by day how many tasks each worker completed and how many they did not. The intake sheet is set up for them to enter the date when they input data so if they are on vacation or just don't do it, no date is present.

This is my summary sheet to add up done and undone checkboxes from above. There is a date and column 2 is Tasks Checked and column 3 is Tasks Unchecked.

The formula in each cell is as follows:

=INDEX({Person AM Completed Tasks}, MATCH(Date@row, {Date})) + INDEX({Person2 AM Competed Tasks}, MATCH(Date@row, {Date 2}))

The problem with this is that on the first screenshot you can see that "Person" did not enter anything on 10/22 to 10/29 (neither did Person2), but the total completed tasks stays as the same number. Why?

What can I do so if a date is missing from the collection sheet, the formula will make tasks completed as 0 and not a running total? It should be what was entered for the specific date and if that date does not exist on Person AM Completed Tasks, it should count as 0.

Thank you!!

Paul Newcome

I would suggest a helper column on the source sheet with a formula that will count how many boxes are checked on each row.

Then on your metrics sheet you can use a SUMIFS to pull the new helper column based on person and date range/criteria sets.