Sumif and Weekday function combined

[unam1978]

Hello,

First time posting, would appreciate some help.

I have a column with dates filled in vertically, lets say 10 or so dates. Lets call this column A. Each date has a corresponding $value in the column next to it. Lets call this column B. So 10 dates, and 10 $values. To the right I have ONE row with 10 dates, in each column heading (Column D through M), each date is 7 days apart. So for example, the first date is 4/9/2015, second date is 4/16/2015, etc all the way to the 10th date.

So I want to create a sumif function where the equation looks at all the dates in the left column (column A) and compares it with the heading date in the row in the first column (column D). If any of the dates in the vertical column (Column A) are within that first week, then sum the corresponding values in the left column. They must only be within those 7 days. Then we move on to the second column, in the row, and the values have to be between 4/9/15 and 4/16/2015, and so forth.

Basically i'm creating a 10 week cashflow statement and I want to know in which week the account receivables will be due and I want to avoid double counting. Thanks.

[benishiryo]

welcome to the forum, unam. perhaps you could key in manually what you are hoping to see next time. maybe in D4:
=SUMIFS($B:$B,$A:$A,">="&D3,$A:$A,"<"&D3+7)

[MarvinP]

Hi unam and welcome to the forum,

This looks like a SumProduct problem to me. Put this in D4 and pull across.

=SUMPRODUCT(--($A$3:$A$12>=D3),--($A$3:$A$12<D3+7),$B$3:$B$12)

I'm not sure what the Weekday has to do with the question.

[unam1978]

Hi Benishiryo,

I changed your formula a little bit from what you gave but I'm still off. The formula I'm putting in =SUMIFS($B:$B,$A:$A,">="&D3-7,$A:$A,"<"&D3) works but doesn't add more than one value. For example in the dates between 5/28/15 - 5/21/15 - there should be two values 34 and 66, to get 100, but I only get 66.

Any recommendations? I added a version 2 to the file. Thanks!

[unam1978]

Hi MarvinP,

I tried your equation and its slightly off. I've added file version 3 with your equation and showed compared to expected results.

Thanks for your help.

[unam1978]

Actually never mind. My fault Benishiryo, the date was 2014. so the new updated formula works. Thanks.

[MarvinP]

Perhaps you need a "<=" (less than or equal to) instead of a single less than. I don't know exactly the answer you are trying to arrive at.

[unam1978]

I got it MarvinP - I just changed it to =SUMPRODUCT(--($A$3:$A$12>=D13-7),--($A$3:$A$12<D13),$B$3:$B$12) from +7 to -7 and that fixed it.

Regarding why I asked for weekday initially, that's how I saw someone resolve it earlier so I was trying to play around it but couldn't figure it out. Its driving me mad. i guess there are multiple ways to solving all these.

The logic behind weekday I guess is that if its withing that week and the week end in a certain day, then it should be included. I just cant get my head around how it was done.

But anyway, thanks for your help - your way works as well.