Hello,,
i tried the below formula. it shows the value 0. but there is value.
=SUMPRODUCT((M:M="*"&D2&"*")*(K:K=O11))
please help me by correcting..
Thanks in Advance..
Hello,,
i tried the below formula. it shows the value 0. but there is value.
=SUMPRODUCT((M:M="*"&D2&"*")*(K:K=O11))
please help me by correcting..
Thanks in Advance..
try it like this
=SUMPRODUCT((ISNUMBER(SEARCH(D2,M2:M100)))*(K2:K100=O11))
"*"&D2&"*" produces a string with * at each end it is not treated as a wild card in sumproduct
Last edited by martindwilson; 01-02-2014 at 09:24 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thanks a lot... you are super.
Happy New Year!!
If you have Excel 2010 why do you not use COUNTIFS:
=COUNTIFS(M:M,"*"&D2&"*",K:K,O11)
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
thats also works...
i have 1 more problem in the data...
Expiration date Member Name
12/15/2013 ABC
12/16/2013 ABC
12/15/2013 ABC
12/15/2013 ABC
12/15/2013 ABC
12/15/2013 ABC
12/15/2013 ABC
12/17/2013 DEF
12/17/2013 DEF
12/17/2013 DEF
12/17/2013 DEF
12/17/2013 DEF
if the data is like above table... i want to remove all the duplicates. but i have problem is that Expiration date is different some time and I need Member with different dates..
I need like this:
Expiration date Member Name
12/15/2013 ABC
12/16/2013 ABC
12/17/2013 DEF
could you please help me with this also...
What is the problem? If you use Remove Duplicates feature it will only remove rows that are entire duplicates.
Its worked....
Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.
In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.
Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.
Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks