3 way look up involving dates.

**Raanan** · 09-03-2013, 04:41 AM

Good morning and thank you for your time.

I have a query which I am struggling to get my head around, I felt I had an index,match to work however I dont.

I have a table with headings similar to below

Handler Name Call volume in Call volume out

The information is on a "raw data" worksheet.

what I need to do is have Call Volume data automatically pull through based on the following conditions.

Name of Handler, Call Volume and between 2 dates (there are data on the data pull)

what is the cleanest formula to work this out?

**Fotis1991** · 09-03-2013, 04:43 AM

Good morning to you too.

Be sure that best way to describe your problem is to upload a sample workbook.

Be sure that all sensitive data removed, showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that.

To attach a small sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Raanan** · 09-03-2013, 04:57 AM

Hopefully this makes sense.

Thanks

(forgot it was protected.. to save space.. Password is.. erm password)

**Fotis1991** · 09-03-2013, 05:03 AM

Perhaps to unprotect your sheet first?

**Raanan** · 09-03-2013, 05:07 AM

d'oh. its password

**Fotis1991** · 09-03-2013, 05:10 AM

Not able to open it...

**Raanan** · 09-03-2013, 05:16 AM

third time lucky...

I think my head has mashed

**Fotis1991** · 09-03-2013, 05:23 AM

Originally Posted by Raanan

third time lucky...

I think my head has mashed

Third time is ok!

Do you need to add these?

=SUMPRODUCT((Combined!$A$2:$A$10=$A4)*(Combined!$D$2:$D$10>=$A2)*(Combined!$D$2:$D$10<=$B2)*(Combined!$C$2:$C$10))

OR just to count the occasions?

=SUMPRODUCT((Combined!$A$2:$A$10=$A4)*(Combined!$D$2:$D$10>=$A2)*(Combined!$D$2:$D$10<=$B2))

**Raanan** · 09-03-2013, 05:34 AM

oh ok yeh.. ha perfect..

Now I have them I can work with them. Thanks fotis.. (ps wanna do this work for me its driving me nuts.)

Not sure I will ever fully understand SumProdcut.. but ill take it

And sorry as a dunce, how does THAT formula know to look at "calls out" volume..

**Fotis1991** · 09-03-2013, 05:47 AM

Originally Posted by Raanan

oh ok yeh.. ha perfect..

Now I have them I can work with them. Thanks fotis.. (ps wanna do this work for me its driving me nuts.)

Not sure I will ever fully understand SumProdcut.. but ill take it

And sorry as a dunce, how does THAT formula know to look at "calls out" volume..

=SUMPRODUCT((Combined!$A$2:$A$10=$A4)*(Combined!$D$2:$D$10>=$A2)*(Combined!$D$2:$D$10<=$B2)*(Combined!$C$2:$C$10))

Formula knows nothing!

We tell to the formula where to look for aur results..See the red part of the formula.

SUMPRODUCT is a great excel tool! If you want to learn it studh the Bob Philips site. Is the best!!

So in this case..

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**Raanan** · 09-03-2013, 05:50 AM

Done and done.. feedback/rep provided and I understand that..

Sorry Im sounding really daft now.. The second formula:
=SUMPRODUCT((Combined!$A$2:$A$10=$A4)*(Combined!$D$2:$D$10>=$A2)*(Combined!$D$2:$D$10<=$B2))

This doesn't have the "row to look at" so if say the call Volume in was empty but out wasnt.. Would I have to adjust the formula for this?

Not sure if a different question as such Im trying to get my head around sum prod

**Fotis1991** · 09-03-2013, 06:00 AM

It's the first part of the formula that gives you this.

=SUMPRODUCT((Combined!$A$2:$A$10=$A4)*(Combined!$D$2:$D$10>=$A$2)*(Combined!$D$2:$D$10<=$B$2))

We tell to the formula to find the results only for Cell A4 that there exists John Name..! The other 2 conditions are to find the days that we need...

Put it in D4 and copy down..

**Raanan** · 09-03-2013, 06:08 AM

sorry I must be completely lost on this (as i usually am when I find a new excel formula)

Can you repost an example as to how the table would look auto populating the amount of call volumes in and call volumes out for john..

Just so I can get my head around it.

I do apologise that i may seem a bit of a dunce, perhaps Im looking too lateral IOm just trying to work out how i would put that formula in the different cells to complete the table that's all

SO I understand how to ADD the cells together thats great..

What I dont get is how to get the formula to count how many times john appears in call volume in. If call volume in was blank and call volume out wasnt?

**Fotis1991** · 09-03-2013, 06:19 AM

No worries..

To be more clear we'll add 1 more condition..

=SUMPRODUCT((Combined!$A$2:$A$10=$A4)*(Combined!$D$2:$D$10>=$A$2)*(Combined!$D$2:$D$10<=$B$2)*(Combined!B$2:B$10<>""))

See the example.

Comments?

**Raanan** · 09-03-2013, 06:28 AM

AHHH!!!

I believe sir.. that has done me perfectly!! And obviosly If i was counting words I could just change the "" to "NO" or whatever?!

Again another excellent example.. Ill add you to my favourites.

thank you VERY VERY much

**Fotis1991** · 09-03-2013, 06:32 AM

Originally Posted by Raanan

AHHH!!!

I believe sir.. that has done me perfectly!! And obviosly If i was counting words I could just change the "" to "NO" or whatever?!

Again another excellent example.. Ill add you to my favourites.

thank you VERY VERY much

YES!

My pleasure! Thank you for your kind words.

**Raanan** · 09-17-2013, 06:00 AM

now I have a slightly different issue. Apologies if this throws a curveball.

the current formula i use counts the times either "L:L" or "M:M" is not blank and is comin up with the sum 7 (at the moment).
I need the sum to count when either L or M (or both) are not blank and count the occasions.
(answer should be 9)

thanks

**Fotis1991** · 09-17-2013, 06:06 AM

In the example sheet that we used for solving your issue we didn;t used columns L & M. What do you mean?

Pls post an example sheet!

**Raanan** · 09-17-2013, 06:14 AM

What I need to work out is column G in WEEKLY.

This needs to work out the amount of times that either "Phone number from Triage" (column L) or/and "Address from Triage" ((column M) on combined) is NOT blank..

so it there is an entry in one or the other OR both it counts the occasions.

**Fotis1991** · 09-17-2013, 06:23 AM

Are you sure that you uploaded the correct workbook?

This sample has validation lists from another workbook...Formula in column G says...nothing....."Compinied" sheet is empty.....

**Raanan** · 09-17-2013, 06:28 AM

G7 on Weekly sheet should read - =SUMPRODUCT((Combined!$A$2:$A$100000=$A7)*(Combined!$G$2:$G$100000>=$A$5)*(Combined!$G$2:$G$100000<=$B$5*(Combined!L$2:L$100000<>"")+(Combined!M$2:M$100000<>"")))

The Combined sheet IS blank I had to remove the data, but essentially you can put anything in the combined sheet for it to work. The validation lists should also have been moved on this instance

**Fotis1991** · 09-17-2013, 06:48 AM

Unfortunately i don't have the time to build a sheet again for testing.... The sheet is really slow as you use hudge reference..G2:G100000 ...?

Just an idea.....

=SUMPRODUCT((Combined!$A$2:$A$100000=$A7)*(Combined!$G$2:$G$100000>=$A$5)*(Combined!$G$2:$G$100000<=$B$5)*(Combined!L$2:L$100000<>"")+(Combined!M$2:M$100000<>""))

The red part of the formula has a non correct reference cell i think. B5??

**Raanan** · 09-17-2013, 07:02 AM

$B$5 is the end date for the between dates its just in white so you cant see.. It's ok Ill try work it out. The reference has to be 10,000 as there will be around 10,000 entries by the end of the year.

3 way look up involving dates.

LinkBack

Thread Tools

Rate This Thread

Display

3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Re: 3 way look up involving dates.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Circular Referencing Involving Dates

[SOLVED] Need IF Formula involving Dates and Accumulators

Calculations involving dates...

Need help with formula involving dates

If Formulas involving dates

Bookmarks

Bookmarks

Posting Permissions