How to use date range to sum multiple columns

[Excel Newbie]

I'm at a loss, I can't get anything to work. Please I could use help from anyone.


"Week
Ending" Web Request Phone Request New to File NTF (Web) Test Drop Ship Less than lb Paid Subs
1/7/2012 842 244 29 451 106 - -
1/14/2012 1,152 255 32 459 132 - -
1/21/2012 - - - - - - -
1/28/2012 - - - - - - -
2/4/2012 - - - - - - -
2/8/2012 1,067 278 30 377 114 - -
2/9/2012 1,189 246 30 349 88 - -
2/10/2012 952 235 18 323 95 - -
2/11/2012 1,200 240 19 378 98 - -
2/18/2012 1,258 229 25 414 85 - -
2/25/2012 1,369 244 26 392 105 3 -
3/3/2012 1,226 223 83 518 151 14 -
3/10/2012 1,435 455 84 543 147 11 -
3/17/2012 1,273 269 56 472 124 4 -
3/24/2012 1,259 283 51 570 159 5 -
3/31/2012 1,196 251 43 508 148 6 -

I need to sum the activity by month so for January the sum should be 3,702. I got the range to work summing one column but not multiple.

 

[alow]

Which version of Excel are you in? If it's an older version, you may need to use an array formula, or a sumproduct. If it's one of the newer versions, you can use the SUMIFS formula.

 

[Excel Newbie]

I'm using 2010. I tried adding an array and it didn't work.

 

[Excel Newbie]

I ended up creating starting and ending dates and added the columns together and got the total I needed but I have to think there is a better way to write this formula.

=SUMPRODUCT(--('[Bulk Usage.xls]JP'!$A$3:$A$57>='[Bulk Usage.xls]Dates'!$B$1),--('[Bulk Usage.xls]JP'!$A$3:$A$57<='[Bulk Usage.xls]Dates'!$C$1),'[Bulk Usage.xls]JP'!$B$3:$B$57+'[Bulk Usage.xls]JP'!$C$3:$C$57+'[Bulk Usage.xls]JP'!$D$3:$D$57+'[Bulk Usage.xls]JP'!$E$3:$E$57+'[Bulk Usage.xls]JP'!$F$3:$F$57+'[Bulk Usage.xls]JP'!$G$3:$G$57+'[Bulk Usage.xls]JP'!$H$3:$H$57+'[Bulk Usage.xls]JP'!$I$3:$I$57+'[Bulk Usage.xls]JP'!$J$3:$J$57)

 

[alow]

A SUMIFS is backwards, my mistake. That takes a sum with multiple columns of criteria. What will work is using the MONTH formula to pull a number for the month of the date (1 through 12) and using that in a SUM array formula. Here's an example with Dates in Column A and numbers in B and C:
=SUM((MONTH(A2:A101)=1)*B2:B101,(MONTH(A2:A101)=1)*B2:B101)

Then press Ctrl+Shift+Enter to make it work.

 

[Excel Newbie]

I should have mentioned that this information is being pulled from another workbook and if I use a sum statement I will get a value error if that worksheet isn't open. Is there a way to avoid getting the value errors?

 

[alow]

After you have the formula in there, go to the Data tab and click on Edit Links, and then Update all links in there. See if that fixes the problem for you.

 

[Excel Newbie]

That did the trick. Thank you so much for your help and quick reply!!!

 

[alow]