Solved

Sum by month

Posted on 2011-03-14
215 Views
I am trying to sum by month based on the attached spreadsheet.  I have tried searching for an answer to this, and when I use what I think is the correct array formula, I get a #Value error.

Thanks,

Michael expert-exchange-datesum-example.xlsx
0
Question by:paxtonm
• 3
• 2
• 2

LVL 24

Expert Comment

ID: 35129907
Which column? Can you clarify more please.
0

Author Comment

ID: 35129917
Sorry, I am trying to sum Column F.
0

Author Comment

ID: 35129963
I have been getting an error using this formula

=SUMPRODUCT((MONTH(\$A\$2:\$A\$225)=MONTH(I1)*(\$B\$2:\$F\$225)))
0

LVL 85

Expert Comment

ID: 35130015
Based on your workbook you would need:
=SUMPRODUCT((MONTH(\$A\$2:\$A\$225)=MONTH(H2))*(\$F\$2:\$F\$225))
0

LVL 85

Accepted Solution

Rory Archibald earned 500 total points
ID: 35130030
Safer to check the year too though, unless you will only have one year's data:
=SUMPRODUCT(--(MONTH(\$A\$2:\$A\$225)=MONTH(H3)),--(YEAR(\$A\$2:\$A\$225)=YEAR(H3)),\$F\$2:\$F\$225)

Note: you could also use a pivot table for a simple summary.
0

LVL 24

Expert Comment

ID: 35130042
Not sure if you have correct columns in that formula:
Column I is empty.
\$B\$2:\$F\$225 this is a wide range.
Could you show a sample of what you expect the formula to do? I am not sure I got you correctly.
0

Author Closing Comment

ID: 35130050
Spot on formula!
0

Featured Post

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…