Sum for a specific date range

[kebabpete]

Hi,

I'd like to create a formula that will give me the total number of a specific action based on a specific date range from another column.

Layout is...
Column A - Transaction Type - Contains: FIW, LIW, etc
Column B - Date of Transaction - Contains: dd/mm/yy
Column C - Qty Transfered - Contains numbers between: 1-9

I've tried naming the columns and using sumproduct to add the total number of FIW transactions for the month of March like...

=SUMPRODUCT((Quantity)*(MONTH(Date)=3)*(Adjustment_Type="FIW"))

But its just returning #VALUE!

I think I might be over complicating this somewhat.

Any help appreciated.

Thanks.

 

[Aladin Akyurek]

You have probably formula blanks in Quantity. Try something like...

=SUMPRODUCT(
  Quantity,
  --(Date-DAY(Date)+1="2012-03-01"),
  --(Adjustment_Type="FIW"))

If you are on Excel 2007 or later...

=SUMIFS(
  Quantity,
  Date,">=2012-03-01",
  Date,"<="&EOMONTH("2012-03-01",0),
  Adjustment_Type,"FIW")

 

[Robert Mika]

Your formla works for me:
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">2</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">3</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">FIW</td><td style="text-align: right;;">3/3/2012</td><td style="text-align: right;;">100</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;;"></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><td style="text-align: right;;"></td></tr></tbody></table>

Sheet1​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F4</th><td style="text-align:left">=SUMPRODUCT((Quantity)*(MONTH(date)=3)*(Adjustment_Type="FIW"))</td></tr></tbody></table></td></tr></tbody></table>
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Workbook Defined Names<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">Adjustment_Type</th><td style="text-align:left">=Sheet1!$A$1:$A$23</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">date</th><td style="text-align:left">=Sheet1!$B$1:$B$23</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">Quantity</th><td style="text-align:left">=Sheet1!$C$1:$C$23</td></tr></tbody></table></td></tr></tbody></table>

 

[kebabpete]

Aladin - Thanks that works for 2007. Never seen that before either.

Robert - Thanks for that. Seeing yours made me realize that I had the column name included in the data range. So of course it wasn't recognizing "Date" as a date format.

Much appreciated.

 

[Aladin Akyurek]

Aladin - Thanks that works for 2007. Never seen that before either.

Robert - Thanks for that. Seeing yours made me realize that I had the column name included in the data range. So of course it wasn't recognizing "Date" as a date format.

Much appreciated.

You are welcome.