I have a simple formula for generating reports on a running sum. There are 4 fields
CODE, DOLLARS, DESCRIPTION, DATE
I have written the following formula to calculate the monthly totals for each CODE.
=SUMIFS(dollars,refcodes,A2,RefDate,">=3/1/2009",RefDate,"<=3/31/2009")
Where dollars, refcodes and refdate are named ranges.
It works but this seems very awkward. Is there a way just to have one criteria that tests RefDate for the month like JAN09 or MAR09?
thx
Peter
I see nothing "awkward" about the formula. However, you might consider using a Pivot Table based on a dynamic named range for the source data.
You could use an array formula:
{=SUM(IF(MONTH(A2:A7)=1,B2:B7))}
Where:
Column-A holds dates
Column-B holds values to sum
"1" in the formula is the month (could be a cell reference to make it dynamic)
Cells references in the above formula, obviously, can be named ranges as you already show.
Note: Array formulas are committed using Ctrl+Shift+Enter key combination. Excel automatically adds the curly brackets.
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
similar question asked yesterday in regard to COUNTIFS (ie SUMIFS without the summation range) so the points hold true I'd say
http://www.excelforum.com/excel-gene...light=COUNTIFS
If your start date and end date ever equate to anything other than a full month then you will struggle to apply a single test based approach even in an Array / Sumproduct.
I would still go further and say that even if you could get away with a single test the Array/Sumproduct would still be less efficient than the equivalent SUMIFS approach with the additional criteria test.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks