Counting wild card string while filtering for a certain date range
Hi,
I want to know how I can calculate the number of times a particular text string appears in a column within a set date range...
e.g.
Column A
01/08/2011
05/08/2011
19/08/2011
23/08/2011
11/09/2011
14/09/2011
20/10/2011
Column B
premium photo book
A4 canvas print
classic photo book
glossy photo book
new photo book
poster print
small photo book
I now want to count the number of times the words "photo book" appear in August (01/08/2011 - 31/08/2011)
In this case it would be 3 as the words "photo book" only appear 3 times in August (imagine column B is next to column A)
Thanks in advance for any help!
Based on your version (2003)
Assuming always looking at complete month:
If partial month:=SUMPRODUCT((TEXT(A1:A100,"mmyyyy")="082011")*ISNUMBER(SEARCH("photo book",B1:B100)))
obviously the hard wired constants can be altered to be cell references which contain parameter values=SUMPRODUCT((A1:A100>=DATE(2011,8,1))*(A1:A100<=DATE(2011,8,15))*ISNUMBER(SEARCH("photo book",B1:B100)))
NOTE: sumproduct is inefficient, should be used in moderation, ideally in non-volatile context and precedent ranges should be kept as lean as possible
If you are using this exclusively in XL2007 or above then revert to COUNTIFS from SUMPRODUCT:
Note here size of precedent range is irrelevant hence use of A:A=COUNTIFS(A:A,">="&DATE(2011,8,1),A:A,"<="&DATE(2011,8,31),B:B,"*photo book*")
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you for the reply!
I'm a bit lost, what exactly do I need to change in the code you gave me?
I'm using excel 2010. How exactly do you get the two columns to relate to one another?
In my case I have a column with dates in it and next to it is the column with the product description,
Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks