Hi
Col A has a list of dates UK Format dd/mm/yyyy
Col C has a unique local codein each cell which is always in the following format 1 xxx yyy (one number and 2 sets of three letters)
I would like a formula that will return how many times the number one occurs in any given month, The attached picture use Feb as an example.
I also want a formula that will return a count of abc and a separate count of yyy.
Hope the picture is obvious
screen.jpg
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
You probably need the COUTIFS and/or COUNTIF function
I have attached a sample file.
I forgot to mention i need the formula to be compatible with Excel 2003 (Dont ask company policy)
Try
=SUMPRODUCT((ISNUMBER(SEARCH("1",$C$1:$C$12)))*(MONTH($A$1:$A$12)=2))
Change "1" to "abc" and "yyy"
John
Magic, works a treat. Many Thanks.
John
your solution works a treat but I have now moved on and need more search criteria.
So
=SUMPRODUCT((ISNUMBER(SEARCH("1",$C$1:$C$12)))*(MONTH($A$1:$A$12)=2)) will find "1" in Col C1-C12 for Feb
Can I seach for "1" in Col C1-C12 AND "a" in Col D1-D12 for Feb. So the result would only be positve if all three conditions were met?
Try this...
=SUMPRODUCT(--ISNUMBER(FIND(1,C$1:C$12)),--ISNUMBER(SEARCH("A",D$1:D$12)),--(MONTH(A$1:A$12)=2))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
=COUNTIFS(C$1:C$12,"*1*",D$1:D$12,">="&DATE(2015,2,1),D$1:D$12,"<"&DATE(2015,3,1))
Please Login or Register to view this content.
try this
It will not work in 2003 version, sorry ignore it
Last edited by samba_ravi; 11-24-2015 at 06:04 AM.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
I think you missed this:Originally Posted by nflsales
you are right @ Tony Valko, I missed the OP comment.
=COUNTIFS($C$1:$C$12,"*def*",$A$1:$A$12,">="&DATE(2015,2,1),$A$1:$A$12,"<"&DATE(2015,3,1))
Looks like you also missed this:
Managed to get this to work so all sorted now. Thanks for all the help. much appreciated.
=SUMPRODUCT(--(ISNUMBER(SEARCH("abc",$C$1:$C$12)))*(ISNUMBER(SEARCH("def",$D$1:$D$12)))*(MONTH($A$1:A$12)=1))
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks