SUMIF combined with Mid Function

[howard]

I have set up a sumif formula. I have tried to use the Mid Function within the sumif to extract the criteria instead of having to type the criteria, but it gives me zero-see formula below

=SUMIF($B$1:$B$9,"<"&MID(B18,30,18)&"",$E$1:$E$9)


If I type in the criteria, i get the correct result-see formula below

=SUMIF($B$1:$B$9,"<25/07/2011",$E$1:$E$9)

I have attached sample data below

It would be appreciated if you could assist me

Chapter 5.SUMIF.xls
	A	B	C	D	E	F
18	TotalInvoiceamountsBefore25/07/2011				0.00	11,597.00
Sheet1

 

[Yahya]

try this
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">01/07/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">15/07/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">25/07/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">01/08/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">14/08/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">14/08/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">18
Total Invoice amounts Before 25/07/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;">300</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D8</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">DATEVALUE(<font color="Green">RIGHT(<font color="Purple">B8,11</font>)</font>)>=B1:B6,E1:E6</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[howard]

Thanks for the help, formula works perfectly.

It is possible to change this formula so that it works in the normal =Sumif formula?

 

[AlphaFrog]

Try something like this...

=SUMIF($B$1:$B$9,"<"&DATEVALUE(MID(B18,30,18)),$E$1:$E$9)

DATEVALUE converts the text date into a serial date.

This does the same thing...
=SUMIF($B$1:$B$9,"<"&MID(B18,30,18)*1,$E$1:$E$9)

 

[Yahya]

Thanks for the help, formula works perfectly.

It is possible to change this formula so that it works in the normal =Sumif formula?

Sa,e with Alpha Formula
=SUMIF(B1:B6,"<="&DATEVALUE(RIGHT(B8,11)),E1:E6)

 

[MrVillareal]

Hi try this simple example.

Excel Workbook
	A	B	C	D
1	Date	Amount		Total Amount Before 8/19/2011
2	8/14/2011	1000		15000
3	8/15/2011	2000
4	8/16/2011	3000
5	8/17/2011	4000
6	8/18/2011	5000
7	8/19/2011	6000
8	8/20/2011	7000
Sheet1

Excel 2010

Cell Formulas
Range	Formula
D2	=SUMIF(A2:A8,"<"&MID(D1,21,LEN(D1)-20)+0,B2:B8)

 

[howard]

Thanks for the help, much appreciated

 

[howard]

Hi Yahya

Thanks for the help, formula works perfectly

Regards

Howard

 

[Yahya]

Hi Yahya

Thanks for the help, formula works perfectly

Regards

Howard

You are welcome