Solved: Using Sumproduct to Calculate Weekly Totals

[Opv]

Is there a way using SUMPRODUCT to extract a weekly total for the daily totals provided below? I am wanting the totals calculated based on a Sunday-Saturday week; however, as the sample shows, the column of dates skips some days (for which there are no transactions).

COL A COL B COL C
ROW DATE DAILY TOTAL WEEKLY TOTAL
3 Fri, 01/15/2010 $40
4 Sat, 01/16/2010 $40 ???
5 Wed, 01/20/2010 $40
6 Thu, 01/21/2010 $40
7 Fri, 01/22/2010 $40
8 Sat, 01/23/2010 $40 ???
9 Sun, 01/24/2010 $40
10 Mon, 01/25/2010 $40
11 Tue, 01/26/2010 $40
12 Wed, 01/27/2010 $40
13 Thu, 01/28/2010 $40
14 Fri, 01/29/2010 $40
15 Sat, 01/30/2010 $40 ???

[Opv]

Ooops, I didn't realize my columns would get so skewed. I'll attach a sample worksheet shortly.

[Opv]

OK, here is a more easily readable sample.

Thanks,

Opv

[Bob Phillips]

Try

=IF(SUMPRODUCT(--($A6:$A$16>=$A5-WEEKDAY($A5)+1),--($A6:$A$16<$A5-WEEKDAY($A5)+8))>0,"",
SUMPRODUCT(--($A$5:$A$16>=$A5-WEEKDAY($A5)+1),--($A$5:$A$16<$A5-WEEKDAY($A5)+8),$B$5:$B$16))

[Opv]

Thanks, but I just realized I left a critical column out of my original sheet. I've uploaded an updated sample sheet. I'm sorry for causing you to go to so much trouble for something that wasn't even what I really wanted.

The newly attached sheet includes a column for names. What makes the needed formula too complicated for me is that I'm wanting the weekly totals applicable only to each person named in Column A. Consequently, I can't just use from-to date or offset, as I have to find a way to extract only the rows applicable to each person to calculate their individual weekly totals.

Again, sorry for the confusion,

Opv

[Bob Phillips]

=IF(SUMPRODUCT(--($A6:$A$16>=$A5),--($B6:$B$16>=$B5-WEEKDAY($B5)+1),--($B6:$B$16<$B5-WEEKDAY($B5)+8))>0,"",
SUMPRODUCT(--($A$5:$A$16>=$A5),--($B$5:$B$16>=$B5-WEEKDAY($B5)+1),--($B$5:$B$16<$B5-WEEKDAY($B5)+8),$C$5:$C$16))

[Opv]

Thanks! So far so good....I'm still testing.

[Opv]

Thanks for the formula. After applying the formula, it seems to work some times and not others. Ive attached an updated sample sheet with the forumla applied. The highlighted rows either reflect no total at all or a total that overlaps figures from the wrong person.

Am I missing something or did I screw something up?

[Opv]

This is a totally different question but still related to Sumproduct. I wasn't sure whether I should have started a new thread or keep issues with the same function together. At any rate, here goes....

The following function works; however, something is going on and I need to know if there is a workaround. First, the formula:

=IF($B$8="All",$B$6,SUMPRODUCT(--(MyContacts!$B$1:$B$5000=SUMMARY!$B$8),--(MyContacts!$W$1:$W$5000="Deceased")))

With respect to the first sumproduct array condition is, i.e., =SUMMARY!$B$8 (which is testing for a value of 1972), is returning a result of 13; whereas, it should actually be 15. The only thing I can figure out is that the array in question contains both text and values. For example, most of the cells in the array contain a four-digit number; however, a few cells contain text strings. All of the cells are formatted as "General" content. I did some testing and if I change the above formula to this, =Value(SUMMARY!$B$8)), the results is 2 (which reflects the 2 missing in the result prior to the change).

I'm not sure how that is happening since all of the cells are supposedly formatted for General content. Regardless, is there a way to force the above formula to capture all 15 instances of 1972 without having to constantly reformat cells as new data rows are created? (New rows are created using VBA, i.e., copying and pasting a previous row, which may mean a text, vs a value, format may be inherited.)

[Opv]

Disregard. I did some further testing and it appears that somehow in the original copying and pasting when the worksheet was created some of the cell values ended up with a trailing space. I applied the TRIM function and now the formula is producing the correct count.