Hi George,
G3 should remain blank as row 3 is not a Saturday.
G4, when the formula is filled down to that row, should show 2.3, the total miles walked in the week ending Saturday June 2.
Here's the formula, with a breakdown of what it does:
G3: =IF(AND(WEEKDAY(A)=7,COUNTIFS($E,">0",$A,">"&A3-7,$A,"<="&A3)>0),SUMIFS($E,$A," >"&A3-7,$A,"<="&A3),"")
IF(condition,doifTRUE,doifFALSE)
condition: AND(condition 1,condition 2)
TRUE only if both condition 1 AND condition 2 are TRUE
condition 1: WEEKDAY(A)=7
TRUE only if the date on this line is a Saturday
condition 2: COUNTIFS($E,">0",$A,">"&A3-7,$A,"<="&A3)>0
COUNTIFS(test values 1,condition 1,test values 2,condition 2,test values 3,condition 3)
Counts only the rows where:
the numerical value in column E is greater than zero (ie. a number has been entered in the miles walked column)
AND the date in column A is after the date seven days before the date in the row containing the formula
AND the date in column A is on or before before the date in the row containing the formula
TRUE only if COUNTIFS returns a count of one or more.
doifTRUE: SUMIFS($E,$A," >"&A3-7,$A,"<="&A3)
sums the values in column E in rows for dates fitting the same conditions for dates in COUNTIFS above.
doifFALSE: ""
inserts a null string (which dispalys as an 'empty' cell) if either of the conditions in AND() is not TRUE.
Regards,
Barry