# Formula for selecting value in cell using multiple criteria?

1. ## Formula for selecting value in cell using multiple criteria?

Hi there,

The situation:

I have a large amount of data regarding room activities on a university campus. The variables are: Day (Mon-Fri), Time (09:00-16:00), Room and Duration (1-9 hours). In worksheet 1, these variables are positioned in columns with each row representing the different activities taking place.

I have tried using logic functions but I cant get it to work. All I want to do is import the duration of events from the first worksheet into that of the second given the selected room, date and time. So, if the room is Room001, the date dd/mm/yyyy, and the time 09:00 then the duration of the event equals x=1 to 9. The answer must be a formula as I have a great deal of info to sort through.

I hope this makes sense. Thank you in advance for any suggestions.

Regards,
PC

2. ## Re: Formula for selecting value in cell using multiple criteria?

Hi PC,

welcome to the forum.

Regards,
DILIPandey
<click on below * if this helps>

3. ## Re: Formula for selecting value in cell using multiple criteria?

Hi DILIPandey,

Example now attached.

Cheers,
PC

4. ## Re: Formula for selecting value in cell using multiple criteria?

okay.. do you need 4 in D3 of "Duration summary" sheet ?

if so, then how and where your are considering date here ?

Regards,
DILIPandey
<click on below * if this helps>

5. ## Re: Formula for selecting value in cell using multiple criteria?

Yes that is correct.

I should not have included date, I am filtering the data using day only (Mon-Fri), I know the date so that is something I would input manually.

Cheers

6. ## Re: Formula for selecting value in cell using multiple criteria?

Okay.. so try using below formula which need to be entered using key combination ctrl shift enter.

Formula:
`Please Login or Register  to view this content.`

see attached:- Example(19).xls

Regards,
DILIPandey
<click on below * if this helps>

7. ## Re: Formula for selecting value in cell using multiple criteria?

Hi again,

Thank you for replying but my problem still remains unsolved. I can not seem to drag and copy the equation to fill all the boxes.
Unfortunately as the data within the spread sheet I am going to be applying the formula to is very large (the example I gave you contains 1% of the rooms, 0.001% of activities) I need an equation that I can simply copy.
Also your equation does not have room as a variable. The formula should contain 3 variables: Room, Time and Day.
Please see attached for what the 'Duration Summary' s/s should look like.

PC

(Please see Example (21) for what happens when I copy the formula supplied)

8. ## Re: Formula for selecting value in cell using multiple criteria?

Hi PC, now I included 3 variables in the below formula:-
Formula:
`Please Login or Register  to view this content.`

see attached:- Example(20).xls

note:-
1) fill up the blanks like I did in left columns
2) you have spelling mis-match for days in both the sheets e.g., thu vs thurs etc

Regards,
DILIPandey
<click on below * if this helps>

9. ## Re: Formula for selecting value in cell using multiple criteria?

It works! Thank you very much, you've added hours to my life! If you have a chance could you provide an explanation of what you did and what the functions mean? No trouble if not.

Thanks again.

PC

10. ## Re: Formula for selecting value in cell using multiple criteria?

Hi PC, I have used Index function and Match function here along with Iferror error handler... suggest you to see excel in-built help to learn these function..

For filling up blanks.. I have selected left column's data and then pressed F5-> selected blanks->Enter. Now = sign and up arrow and pressed ctrl + enter.

Hope this helps.

Regards,
DILIPandey
<click on below * if this helps>

There are currently 1 users browsing this thread. (0 members and 1 guests)