Sumifs betweens and matching criteria

E

el_ja

Board Regular
Joined
Nov 5, 2007
Messages
80
Hello again guys,
I usually just need to sum values between dates but now I'm in the need of adding a criteria, normally what I use is something like this:
=SUMIFS($H:$H;$A:$A;"<="&B5;$A:$A;">"&C5)
Where I only have one column with values H, and A the one with the dates, and B5 and C5 are the range of dates.
But now my data looks like this:

Sheet1
<table style="width: 541px; height: 180px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:1426;width:29pt" width="39"> <col style="mso-width-source:userset;mso-width-alt:2413;width:50pt" width="66"> <col style="mso-width-source:userset;mso-width-alt:1097;width:23pt" width="30"> <col style="mso-width-source:userset;mso-width-alt:5997;width:123pt" width="164"> <col style="mso-width-source:userset;mso-width-alt:9910;width:203pt" width="271"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:1755;width:36pt" width="48"> <col style="width:60pt" span="4" width="80"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt;width:59pt" height="20" width="78">DATE</td> <td class="xl70" style="width:50pt" width="66">CODE</td> <td style="width:36pt" align="center" width="48">Dataa
</td> <td style="width:60pt" align="center" width="80">Datab
</td> <td class="xl69" style="width:60pt" align="center" width="80">Datac</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt" height="20">4-Jan-11</td> <td class="xl70">SOC</td> <td>
</td> <td class="xl69" align="right">65,00 </td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt" height="20">4-Jan-11</td> <td class="xl70">MML</td> <td>
</td> <td class="xl69" align="right">2,00 </td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt" height="20">4-Jan-11</td> <td class="xl70">MML</td> <td>
</td> <td class="xl69" align="right">25,00 </td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt" height="20">15-Jan-11</td> <td class="xl70">CLM</td> <td>
</td> <td>
</td> <td class="xl69" align="right">76,00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt" height="20">15-Jan-11</td> <td class="xl70">SOC</td> <td>
</td> <td>
</td> <td class="xl69" align="right">35,00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt" height="20">16-Jan-11</td> <td class="xl70">VEH</td> <td class="xl69" align="right">22,00 </td> <td class="xl69">
</td> <td class="xl69">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt" height="20">25-Jan-11</td> <td class="xl70">MML</td> <td>
</td> <td class="xl69" align="right">70,00 </td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt" height="20">28-Jan-11</td> <td class="xl70">VEH</td> <td class="xl69" align="right">22,00 </td> <td class="xl69">
</td> <td class="xl69">
</td></tr></tbody></table>

So I'm trying to sum by date ranges and codes so I end up with a table like:
<table border="0" cellpadding="0" cellspacing="0" width="480"><tbody><tr height="20"></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt;width:60pt" height="20" width="80">CODE </td><td class="xl71" style="width:60pt" align="right" width="80">30-Jan-11</td><td class="xl71" style="width:60pt" align="right" width="80">23-Jan-11</td><td class="xl71" style="width:60pt" align="right" width="80">16-Jan-11</td><td class="xl71" style="width:60pt" align="right" width="80">9-Jan-11</td><td class="xl71" style="width:60pt" align="right" width="80">2-Jan-11</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">SOC</td><td>
</td><td>
</td><td>35
</td><td>65
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">CLM</td><td>
</td><td>
</td><td>76
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">MML</td><td>70
</td><td>
</td><td>
</td><td>27
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">VEH</td><td>22
</td><td>
</td><td>22
</td><td>
</td><td>
</td></tr></tbody></table>
I already tried something like :
=SUMIFS('Sheet 1'!$C:$E;'Sheet 1'!$A:$A;"<="&B1;Sheet 1'!$A:$A;">"&B2;'Sheet 1'!$B:B;"="&A2)

Any help on this one
thanks
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
That one for example belongs to the SOC code and falls between 16-jan-11 and 9-jan-11.
The 65 belongs to SOC and is between 9-jan-11 and 2-jan-11

Sorry for not aligning them correctly, and can't seem to find a way to edit my post.
 
Upvote 0
el_ja said:
That one for example belongs to the SOC code and falls between 16-jan-11 and 9-jan-11.
The 65 belongs to SOC and is between 9-jan-11 and 2-jan-11

Sorry for not aligning them correctly, and can't seem to find a way to edit my post.
Click to expand...

<TABLE style="WIDTH: 329pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=438><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2787" span=3 width=78><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2474" span=2 width=70><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>CODE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=78>30-Jan-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=78>23-Jan-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=78>16-Jan-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=70>9-Jan-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=70>2-Jan-11</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>SOC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>65</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>CLM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>76</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>MML</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>70</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>VEH</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR></TBODY></TABLE>

B2, control+shift+enter, not just enter, copy across, and down:
Rich (BB code): 
=SUM(IF(Sheet1!$B$2:$B$9=$A2,IF(Sheet1!$A$2:$A$9<=B$1,
    IF(Sheet1!$A$2:$A$9>=IF(C$1="",B$1,C$1),Sheet1!$C$2:$E$9))))
 
Upvote 0
Seems to work like a charm, unfortunately I have to go. But tomorrow I'll do some more tests on it.
Thank you very very much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
Deepakchoudhary
  • Solved
RANK based on selling multiple items in different criteria (Excel 2016)
Replies
3
Views
162
hagia_sofia
hagia_sofia
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,603
Messages
6,179,853
Members
452,948
Latest member
UsmanAli786

Share this page

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