Google Sheet Weekly Report Troubleshooting - Subtask 1

Description

…the Weekly section in the Executive Overview isn’t pulling in any data, even though they copied the formulas over and dragged them across.

1. Correct the formula(s) in the Weekly section in the Executive Overview so they pull in the proper data.

Solution

Correct the data cells referenced in each formula. For example, C14 in Executive Overview sheet:

Change:

=SUMIFS('Raw Data Combined'!C:C,'Raw Data Combined'!$A:$A,">="&$A14-6,'Raw Data Combined'!$A:$A,"<="&$A14,'Raw Data Combined'!$I:$I,"<>#N/A")

To:

=SUMIFS('Raw Data Combined'!D:D,'Raw Data Combined'!$A:$A,">="&$A14-6,'Raw Data Combined'!$A:$A,"<="&A$14,'Raw Data Combined'!$I:$I,"<>#N/A")

Explanation

From the Google Documentation:

=SUMIFS returns the sum of a range depending on multiple criteria.

Let’s take a look at the syntax:

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

1. sum_range - The range to be summed.
2. criteria_range1 - The range to check against criterion1.
3. criterion1 - The pattern or test to apply to criteria_range1.
4. criteria_range2, criterion2, … - [ OPTIONAL ] - Additional ranges and criteria to check.

In the example from above, B14 is in the Clicks column with sum_range='Raw Data Combined'!C:C which references all rows in column C of the Raw Data Combined sheet. Looking at the Raw Data Combined sheet, we see Cicks data is actually in column D.

The remaining arguments that reference columns in the Raw Data Combined sheet appear to be correct. Given the desired formula output is weekly, it makes sense criteria_range1 and criteria_range2 reference the Date column (A) in Raw Data Combined.

For the purpose of this task, we can assume criteria_range3 is also correct. 'Raw Data Combined'!$I:$I,"<>#N/A" essentially means ignore rows that contain potentially erroneous and/or missing values in the Grouping column (I).

Changing sum_range to 'Raw Data Combined'!D:D now pulls in the correct data for the Clicks column.

We repeat the process using the fill handle for all the cells in the table to ensure they reference the correct columns in the Raw Data Combined sheet.

The Executive Overview sheet now looks like this:

Unfortunately, even though the formulas are now referencing the proper raw data, the table values are still not correct. Let’s look at the two remaining arguments in Subtask 2.