# Calculate amounts from different dates

#### WilliamD93

##### New Member
In the worksheet below I have a list of payment amounts and the due date that they are due on. In cell E2 if have this formula: =SUMPRODUCT((DAY(\$B\$2:\$B\$75)=D2)*\$A\$2:\$A\$75) and it follows on down to the bottom of the list. This makes it so I can tell how much I need for each day of the month. Again look at the copy of this worksheet then scroll down to the next.

 A B C D E F 1 Group 1 Amount Due Date 2 979.34 06/12/2015 1 5,610.30 <- this formula is here =SUMPRODUCT((DAY(\$B\$2:\$B\$75)=D2)*\$A\$2:\$A\$75) 3 979.34 06/12/2015 2 2,716.73 4 980.26 06/12/2015 3 3,473.99 5 938.52 06/12/2015 4 4,204.11 6 911.95 06/12/2015 5 583.93 7 336.48 06/13/2015 6 1,974.25 8 2,961.68 06/14/2015 7 1,504.65 9 492.87 06/14/2015 8 1,996.15 10 506.33 06/14/2015 9 5,457.45 11 1,088.21 06/14/2015 10 1,239.91 12 4,344.78 06/14/2015 11 0.00 13 536.85 06/15/2015 12 7,489.42 14 3,314.92 06/15/2015 13 3,542.97 15 485.53 06/16/2015 14 12,262.11 16 1,599.53 06/17/2015 15 7,331.72 17 659.00 06/17/2015 16 485.53 18 799.16 06/17/2015 17 3,057.69 19 921.21 06/18/2015 18 921.21 20 842.27 06/19/2015 19 3,895.91 21 553.37 06/19/2015 20 1,101.00 22 551.30 06/19/2015 21 0.00 23 539.96 06/19/2015 22 0.00 24 644.58 06/19/2015 23 3,398.79 25 1,101.00 06/20/2015 24 733.45 26 915.85 06/23/2015 25 2,528.17 27 1,048.11 06/23/2015 26 1,765.03 28 879.42 06/23/2015 27 2,465.81 29 555.41 06/23/2015 28 0.00 30 733.45 06/24/2015 29 928.61 31 2,018.00 06/25/2015 30 5,732.06 32 510.17 06/25/2015 31 0.00 33 1,149.07 06/26/2015 Total 86,400.95 34 615.96 06/26/2015 35 1,025.77 06/27/2015 36 1,440.04 06/27/2015 37 928.61 06/29/2015 38 4,956.01 06/30/2015 39 776.05 06/30/2015 40 2,031.66 07/01/2015 41 905.00 07/01/2015 42 2,673.64 07/01/2015 43 1,191.86 07/02/2015 44 996.15 07/02/2015 45 528.72 07/02/2015 46 595.84 07/03/2015 47 1,102.81 07/03/2015 48 708.95 07/03/2015 49 1,066.39 07/03/2015 50 951.06 07/04/2015 51 1,188.33 07/04/2015 52 1,188.33 07/04/2015 53 876.39 07/04/2015 54 583.93 07/05/2015 55 1,974.25 07/06/2015 56 894.04 07/07/2015 57 610.61 07/07/2015 58 783.12 07/08/2015 59 698.15 07/08/2015 60 514.88 07/08/2015 61 1,165.48 07/09/2015 62 4,291.97 07/09/2015 63 397.95 07/10/2015 64 841.96 07/10/2015 65 1,277.17 07/12/2015 66 1,422.84 07/12/2015 67 1,287.34 07/13/2015 68 998.80 07/13/2015 69 460.30 07/13/2015 70 460.05 07/13/2015 71 1,204.06 07/14/2015 72 867.43 07/14/2015 73 796.75 07/14/2015 74 3,479.95 07/15/2015 75 764.43 07/19/2015 76 86,400.95

<tbody>
</tbody>

After I have calculated these numbers I have tallied them in another worksheet as shown below…

 A B C D E 1 Day of Mo. Group 1 Group 2 Group 3 Total 2 1 5,610.30 13,077.87 0.00 18,688.17 3 2 2,716.73 0.00 0.00 2,716.73 4 3 3,473.99 1,408.72 0.00 4,882.71 5 4 4,204.11 0.00 0.00 4,204.11 6 5 583.93 585.48 0.00 1,169.41 7 6 1,974.25 0.00 0.00 1,974.25 8 7 1,504.65 0.00 0.00 1,504.65 9 8 1,996.15 1,116.97 0.00 3,113.12 10 9 5,457.45 0.00 0.00 5,457.45 11 10 1,239.91 0.00 0.00 1,239.91 12 11 0.00 755.66 0.00 755.66 13 12 7,489.42 1,192.96 0.00 8,682.38 14 13 3,542.97 0.00 0.00 3,542.97 15 14 12,262.11 0.00 0.00 12,262.11 16 15 7,331.72 2,307.47 2,589.96 12,229.15 17 16 485.53 1,000.00 0.00 1,485.53 18 17 3,057.69 0.00 0.00 3,057.69 19 18 921.21 4,435.72 0.00 5,356.93 20 19 3,895.91 1,089.58 0.00 4,985.49 21 20 1,101.00 12,106.17 0.00 13,207.17 22 21 0.00 3,419.35 0.00 3,419.35 23 22 0.00 0.00 0.00 0.00 24 23 3,398.79 0.00 0.00 3,398.79 25 24 733.45 3,826.15 0.00 4,559.60 26 25 2,528.17 3,665.42 0.00 6,193.59 27 26 1,765.03 0.00 0.00 1,765.03 28 27 2,465.81 0.00 1,322.94 3,788.75 29 28 0.00 0.00 0.00 0.00 30 29 928.61 0.00 0.00 928.61 31 30 5,732.06 8,055.08 0.00 13,787.14 32 31 0.00 0.00 0.00 0.00 32 Total 86,400.95 58,042.60 3,912.90 148,356.45

<tbody>
</tbody>

Now, I want to calculate in another worksheet the cash-forecasting and out-lay. I have outlined my cash out-lay worksheet by the week starting with 5/31/15. I need a formula that will show how much I need for each week for each Group as shown below.

 A B C D E F G H I J K L M N O P 1 Week Beginning 05/31/15 06/07/15 06/14/15 06/21/15 06/28/15 07/05/15 07/12/15 07/19/15 07/26/15 08/02/15 08/09/15 08/16/15 08/23/15 08/30/15 09/06/15 2 Group 1 3 Group 2 4 Group 3

<tbody>
</tbody>

I have tried to use the SUMIFS formula and I've tried to use the SUMPRODUCT formula such as this: =SUMPRODUCT(('Notes Payable'!\$A\$2:\$A\$32=DAY(B\$1))*'Notes Payable'!\$B\$2:\$B\$32), but it will only calculate one day's payments on not the whole week. I am stumped on how to get it to calculate all the payments in that week's time period. If you could find some information soon on this it would be very beneficial.

Thank you for taking the time to decipher my problem.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Replies
0
Views
284
Replies
1
Views
250
Replies
0
Views
203
Replies
3
Views
373
Replies
1
Views
342

1,195,681
Messages
6,011,129
Members
441,586
Latest member
rodsin76

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back