sumif with date range (colomn and row)

gumaltys

New Member
Joined
Jun 17, 2015
Messages
5
Hi,

I would need help in writing formulae to sum the amount to be paid by clients by referring to the month and year as per output table


Raw data

Amount 2500 3500
1-May-15 1-May-15
1-August-15 1-June-15
1-November-15 1-September-15
1-February-16 1-December-15
1-May-16 1-March-16
1-August-16 1-June-16
1-November-16 1-September-16
1-December-16


Expected Result
2015 2016
January
February
March 3500
April
May 6000 2500
June 3500 3500
July 0 0
August 2500 2500
September 3500 3500
October 0 0
November 2500 2500
December 3500 3500



Need formulae
2015 2016
January ? ?
February ? ?
March ? ?
April ? ?
May ? ?
June ? ?
July ? ?
August ? ?
September ? ?
October ? ?
November ? ?
December ? ?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sure...follow that link and it'll take you to a place (OneDrive I think), download the add-in, install it according to the instructions in that link, then create your spreadsheet example and copy it to this website. Thus will makes it all so much easier for the community to comprehend the needs of your queries. Good luck!!!
 
Upvote 0
HTML Data is here ( Not sure why there is a so much of space down here )

Raw data :

<style type="text/css">
.tg {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
</style>
<table class="tg">
<tr>
<th class="tg-031e">Payment<br> Received</th>
<th class="tg-031e">2500</th>
<th class="tg-031e">3500</th>
</tr>
<tr>
<td class="tg-031e">Months</td>
<td class="tg-031e">1-May-15</td>
<td class="tg-031e">1-May-15</td>
</tr>
<tr>
<td class="tg-031e"> </td>
<td class="tg-031e">1-August-15</td>
<td class="tg-031e">1-June-15</td>
</tr>
<tr>
<td class="tg-031e"> </td>
<td class="tg-031e">1-November-15</td>
<td class="tg-031e">1-September-15</td>
</tr>
<tr>
<td class="tg-031e"> </td>
<td class="tg-031e">1-February-16</td>
<td class="tg-031e">1-December-15</td>
</tr>
<tr>
<td class="tg-031e"> </td>
<td class="tg-031e">1-May-16</td>
<td class="tg-031e">1-March-16</td>
</tr>
<tr>
<td class="tg-031e"> </td>
<td class="tg-031e">1-August-16</td>
<td class="tg-031e">1-June-16</td>
</tr>
<tr>
<td class="tg-031e"> </td>
<td class="tg-031e">1-November-16</td>
<td class="tg-031e">1-September-16</td>
</tr>
<tr>
<td class="tg-031e"> </td>
<td class="tg-031e"> </td>
<td class="tg-031e">1-December-16</td>
</tr>
</table>


Expected result:

<style type="text/css">
.tg {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;}
</style>
<table class="tg">
<tr>
<th class="tg-031e">Result Required</th>
<th class="tg-031e">2015</th>
<th class="tg-031e">2016</th>
</tr>
<tr>
<td class="tg-031e">January</td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e">February</td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e">March</td>
<td class="tg-031e"></td>
<td class="tg-031e">3500</td>
</tr>
<tr>
<td class="tg-031e">April</td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e">May</td>
<td class="tg-031e">6000</td>
<td class="tg-031e">2500</td>
</tr>
<tr>
<td class="tg-031e">June</td>
<td class="tg-031e">3500</td>
<td class="tg-031e">3500</td>
</tr>
<tr>
<td class="tg-031e">July</td>
<td class="tg-031e">0</td>
<td class="tg-031e">0</td>
</tr>
<tr>
<td class="tg-031e">August</td>
<td class="tg-031e">2500</td>
<td class="tg-031e">2500</td>
</tr>
<tr>
<td class="tg-031e">September</td>
<td class="tg-031e">3500</td>
<td class="tg-031e">3500</td>
</tr>
<tr>
<td class="tg-031e">October</td>
<td class="tg-031e">0</td>
<td class="tg-031e">0</td>
</tr>
<tr>
<td class="tg-031e">November</td>
<td class="tg-031e">2500</td>
<td class="tg-031e">2500</td>
</tr>
<tr>
<td class="tg-031e">December</td>
<td class="tg-031e">3500</td>
<td class="tg-031e">3500</td>
</tr>
</table>
 
Last edited:
Upvote 0
Well that wasn't the MTML Maker, but I worked with it anyway. You can see here how HTML Maker adds data that others can easily cut and paste into their spreadsheets to do experiments. This is what I created.

Note that I think your "Required Results" table missed February. I hope this helps a bit:


Excel 2012
ABC
1Payment Received25003500
2MonthsMay 1, 2015May 1, 2015
3August 1, 2015June 1, 2015
4November 1, 2015September 1, 2015
5February 1, 2016December 1, 2015
6May 1, 2016March 1, 2016
7August 1, 2016June 1, 2016
8November 1, 2016September 1, 2016
9December 1, 2016
10
11
12Results20152016
13January00
14February02500
15March03500
16April00
17May60002500
18June35003500
19July00
20August25002500
21September35003500
22October00
23November25002500
24December35003500
Sheet44
Cell Formulas
RangeFormula
B13=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A13&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A13&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
B14=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A14&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A14&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
B15=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A15&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A15&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
B16=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A16&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A16&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
B17=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A17&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A17&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
B18=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A18&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A18&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
B19=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A19&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A19&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
B20=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A20&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A20&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
B21=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A21&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A21&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
B22=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A22&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A22&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
B23=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A23&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A23&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
B24=SUMPRODUCT((DATE(B$12,MONTH(DATEVALUE($A24&" 1, "&B$12)),1)=$B$2:$B$9)*B$1+(DATE(B$12,MONTH(DATEVALUE($A24&" 1, "&B$12)),1)=$C$2:$C$9)*C$1)
C13=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A13&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A13&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
C14=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A14&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A14&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
C15=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A15&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A15&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
C16=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A16&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A16&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
C17=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A17&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A17&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
C18=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A18&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A18&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
C19=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A19&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A19&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
C20=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A20&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A20&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
C21=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A21&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A21&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
C22=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A22&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A22&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
C23=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A23&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A23&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
C24=SUMPRODUCT((DATE(C$12,MONTH(DATEVALUE($A24&" 1, "&C$12)),1)=$B$2:$B$9)*B$1+(DATE(C$12,MONTH(DATEVALUE($A24&" 1, "&C$12)),1)=$C$2:$C$9)*C$1)
 
Upvote 0
Cool...glad it worked for you. And don't forget to install HTML Maker and learn to use it here...come back often with your questions.
 
Upvote 0
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
Payment Received
2500
3500
Result Required
2015
2016
2​
Months
1-May-15​
1-May-15​
January
0​
0​
3​
1-Aug-15​
1-Jun-15​
February
0​
2500​
4​
1-Nov-15​
1-Sep-15​
March
0​
3500​
5​
1-Feb-16​
1-Dec-15​
April
0​
0​
6​
1-May-16​
1-Mar-16​
May
6000​
2500​
7​
1-Aug-16​
1-Jun-16​
June
3500​
3500​
8​
1-Nov-16​
1-Sep-16​
July
0​
0​
9​
1-Dec-16​
August
2500​
2500​
10​
September
3500​
3500​
11​
October
0​
0​
12​
November
2500​
2500​
13​
December
3500​
3500​

G2, control+shift+enter, not just enter, copy across, and down:

=SUM(IF($B$2:$C$9=("1-"&$F2&"-"&G$1)+0,$B$1:$C$1))

If so desired, custom format formula cells as:

[=0]"";General
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top