• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Criteria matching (Date Ranges)

M

mrzoogle

Member
Hi All,


I am having some serious problem with multiple criteria matching. What I want to do is to match three criteria (Start Dates (A1),End Dates (B1) & User names (C1)). The results is in (D1) and each row is unique.


The current formula which I am using to multiple match is as below:


INDEX($D$3:$D$3000,MATCH($G$1&$H$1&$I$1,$A$1:$A$3000&$B$1:$B$3000&$C$1:$C$3000,0)


The problem with this formula is that the date range have to be exact, if not it won't show the cost data.


For example if the cost of user A is £10 between 01/01/2012 & 31/01/2012 it will give £10 result but if you change the date range to 02/01/2012 & 28/01/2012 the formula will no longer work. I assume because I have done the exact match in criteria? but I don't know how to have both greater than for start date and less than for end date in match formula.


What I want this formula to look like is if I select 02/01/2012 & 28/01/2012 it will still give me £ 10 result. Is it possible to do that ?


I can't use sumifs formula because all the data in Col D is unique and can't be summed.


Thanks so much for your time and looking forward to get reply soon.


Regards.
 
MrZoogle


I would use either

=SUMPRODUCT(($A$1:$A$3000=G1)*($B$1:$B$3000=H1)*($C$1:$C$3000=I1),(D3:D3002))

or

=SUMIFS(D3:D3002,$A$1:$A$3000,G1,$B$1:$B$3000,H1,$C$1:$C$3000,I1)
 
Hi Hui,


Thanks for the reply. Unfortunately this formula is still not giving me the answer I want. So for example if I use the sumproduct/sumifs formula and change the date range it will give us zero. Literally I will have to add 30 lines for each month just to show the data for all the possible date and it's just for one client. My apologies if I was not very clear on what I wanted exactly. I've attached the spreadsheet with comments for your reference this time. Thanks again for your time and hope to get reply soon.


http://www.mediafire.com/?zis7y5fxqi7zjr1


Regards.
 
Hi Guys,


I have been playing with quite alot of formulae since the last chat but still haven't got quite what I really want. I really need to solve this thing asap. Please can i have some idea on this ? :)


Cheers !
 
=SUMPRODUCT((C2:C8<=J9)*(D2:D8>=K9)*(E2:E8=I10),(F2:F8)) for you single cell in your example file.


I had to change all of your dates to English US with the same selection of format for this to work. I couldn't get it to work with English UK dates. However, once I made all of the dates the same cell format, the above formula worked. Maybe your data is formatted differently in some of the cells?


HTH,

Don
 
Hi DonMinter,


Thanks for helping me with this, though the formula you've provided is awesome this doesn't help my case.


Is it possible to modify the following formula


=INDEX(F2:F8,MATCH(J9&K9&I11,C2:C8&D2:D8&E2:E8,0))


to something like


=INDEX(F2:F8,MATCH(J9&K9&I11,">="C2:C8&"<="D2:D8&E2:E8,0))


or


=INDEX(F2:F8,MATCH(J9,C2:C8,-1)&MATCH(K9,D2:D8,1)&MATCH(I9,E2:E8,0)) ??


Thanks for your time and help.


Regards.
 
A match will only find matching data. The formula I provided works as you've asked by allowing dates within a range to work. It must be equal to or later than your start date, but less than or equal to your end date, and the user must match. It doesn't actually "sum" anything.


What my formula says is: if the start date is earlier than or the same as the given start date, AND the end date is later than or equal to the given end date, AND the userName is equal to the given user name, give me the Cost associated for that month.


If that's not what you want, then please modify my logical paragraph/statement above to describe what you need.


I had some time (after worktime, here), and I put ALL of your dates into English UK, and my formula worked as I have outlined it in words above. Please let me know where my formula lacks in your criteria.


Don
 
Hi Don,


Thanks for following up with me on this. Your formula is absolutely perfect. How I actually want the formula to work is:


For example I have a date range of: 01/01/2012 - 31/01/2012 User is A & £ 10 cost


We can interpret this as User A have cost of £ 10 From 01-31 Jan 2012. That basically means all the possibility of the date combination within 01-31, no matter what date range it is, if it falls into >=01 & <=31 Jan 2012 the cost is £ 10 for User A.


Hope I have interpreted it clearly :) Now the formula you've provided works great if you have an exact matching date criteria ie. (01/01/2012 - 31/01/2012 from my attachment it will give £ 10 cost result but if I change it to 01/01/2012 - 28/01/2012 I still want it to show £ 10 result, if possible :) )


Thanks again for your time and looking forward to learn a new formula.


Regards,


Z.
 
I must be stupid. I put those date values into J9 and K9 on your spreadsheet, and I still got 10 in J10. I had to select all of the dates in B2:D8 and insure the Date format was EXACTLY the same as the Date format was in J9:K9.


I hope you understand what I am saying. The date formats in your example spreadsheet did not match in my download. Maybe it was a fluke. But when I selected EXACTLY the same (English UK, 3/14/01) for both data sets, it all worked with my formula the way you're stating.


I guess some simple questions and MY answers:


Is J9 your desired start date? YES

Is K9 your desired end date? YES

Is I10:I15 the range of users you want to search? YES

Is A2:F8 the data range you want to search? YES


My formula works for the above if J9 is after the start date in the data block, AND

it works as long as the above plus K9 is before the end date, AND

it works as long as the two above plus the username is EQUAL to the value in I10.


Note that "after" and "before" above include "equal to," not just before or after.


This formula can be copied to the cells below J10 in your example by putting it into J10 and dragging it. It should then pick out any cost that falls in the date range for that username.


=SUMPRODUCT((C2:C8<=$J$9)*(D2:D8>=$K$9)*(E2:E8=I10),(F2:F8))


I don't know where this is not working for you. I am trying to help. Please excuse my problem with understanding what you want to happen.


Don
 
Hey DonMinter,


You are star !! I have found what the issue. I thought my spreadsheet might have been acting weird as you've mentioned about some formatting issue. I started a fresh spreadsheet and Voila !!


Thanks again for your awesome input onto this and I will update you once again in the morning/evening (after the testing on a large scale). :))


Regards,


Z.
 
Hi mrzoogle,


Have you tried the last formula mentioned on this thread? Have a look at this worksheet I think the problem has been fixed with rather a different appraoch!!


http://dl.dropbox.com/u/60644346/mrzoogle_resolved.xlsx


Regards,

Faseeh
 
Hi DonMinter,


This formula is great and works perfectly but now I am in a situation where I need to look at codes (text strings) and I can't make it to work. I tried Vlookup & Index matching, they both works fine only for one condition. Can you guide me which formula to look at for these type of text strings matching ?


Thanks for your time.


@Faseeh thanks for your effort it works fine as well, learned something new :)


Cheers !
 
You must log in or register to reply here.
Back
Top