Afternoon all,
Sheet1
A:A has dates formatted as *Wednesday, March 14, 2001
B:B has Text values which are any of a number of categories
E:E has a Number value
Sheet 2
Looking to sum all values in 'Sheet1'!E:E That match the wildcard *September* in 'Sheet1'!A:A and exact value "Value" in 'Sheet1'!B:B
Click here to read the Forum Rules
Whatever it is in life you decide to go after, go after with great ferocity.
Try
=Sumproduct(--(Month(Sheet1!A:A)=9),--(Sheet1!B:B="Value"),Sheet1!E:E)
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
If the date should be September in any year you could try SUMPRODUCT like this
=SUMPRODUCT((MONTH(A2:A100)=9)*(B2:B100="Value"),E2:E100)
or in Excel 2007 you can use SUMIFS if you want to sum for September 2009,
=SUMIFS(E:E,A:A,">="&DATE(2009,9,1),A:A,"<="&DATE(2009,9,30),B:B,"Value")
...and further to NBVC's SUMPRODUCT if by "Exact" you mean case sensitive (ie Value <> value) then SUMPRODUCT with EXACT should work.
(though I'd add that if you do use SUMPRODUCT I'd personally be inclined to keep ranges as lean as possible - ie avoid entire col. references if possible)
Last edited by DonkeyOte; 12-14-2009 at 02:57 PM. Reason: typo
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
3 great solutions. Thank you very much. I wound up using daddylonglegs Sumifs soultion for 2 reasons.
I was getting errors with the sumproduct and the double --
and
If I ever needed to use the formula with overlapping or smaller date ranges it appeared to be the easiest to modify. eg. summing the numbers for a quarter or 2 week period.
Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks