wow, complicated

[mindfullsilence]

Hello everyone, new member here, how are all of you?

I need excel to look for a number in several ranges and return the number of occurrences that number is found. It's for a work schedule.

Example:
................Wednesday..............
cell..|.......A.........|.......B.......|
1.....|...Time in:....|...7:00 am..|
2.....|...Tim Out:...|..10:00 am.|
3.....|...Time in:....|...7:00 am..|
4.....|...Tim Out:...|..10:00 am.|
5.....|...Time in:....|..10:00 am.|
6.....|...Tim Out:...|...3:00 pm..|

I would need excel to test the ranges for "Time In" and "Time Out" and see if, say, 9:00 AM fell inside the range, and because it does, it would give me a "2." one for B1:B2, and another for B3:B4. After testing B5:B6 it wouldn't add anything because 9:00 AM does not fall between 10:00 AM and 3:00 PM.

I need it to test about 20 of these ranges and tell me how many times "9:00 am" exists.

I have to have this formula for every hour of the day from 7am - 2am. And for each day of the week, so I'm hoping it's simple.

Any ideas?

btw, I have no idea about macros or [COLOR=blue ! important][FONT=Verdana,Helvetica,sans-serif][COLOR=blue ! important][FONT=Verdana,Helvetica,sans-serif]vba[/FONT][/FONT][/color][/color] so I'm hoping this is possible with a formula. I'm definitely not an expert but I'm a little above average on formulas I think.

I'm trying to be as clear as possible without implying that I have little confidence all of you, but I've posted on a couple other forums to no avail. Perhaps I'll strike gold here. If it would help I can post a link to download my file I need this for.

Thanks in advance.

 

[barry houdini]

You probably get #NAME? error because ISODD and ISEVEN are Analysis ToolPak functions. You'll get that error if the add-in isn't installed

....but you can't use those here anyway because they only work with single values not ranges.

You can use MOD function instead, note you only have to check one range because if E5:E35 is odd then the corresponding cell in E6:E36 must be even....therefore you can use

=SUMPRODUCT(($E5:$E35<=P24)*($E6:$E36>=P24)*(MOD(ROW($E5:$E35)-ROW($E5),2)=0))

Note: you need to continue to show all times after midnight as on the next day otherwise this formula won't function correctly....

 

[iliace]

iliace, that formula, after adjusting cell numbers to fit my file, gave me a #name? error.

I have it setup like this now...

"=SUMPRODUCT(--($E$5:$E$35<=P24),--($E$6:$E$36>=P24),--ISODD(ROW($E$5:$E$35)))"

Where E5 is the First Time In and E35 is the last Time In, E6 is the first Time Out and E36 is the last Time Out. P24 is where I have the time I'm looking for.

any ideas?

Yeah,

I did this in Excel 2007, which has the ISEVEN and ISODD functions; in previous versions, you have to install analysis toolpak to get them. This will work:

=SUMPRODUCT(--($E$5:$E$35<=P24),--($E$6:$E$36>=P24),MOD(ROW($E$5:$E$35),2),1-MOD(ROW($E$6:$E$36),2))

However, looking at your spreadsheet, I don't see where the hour value is in P24. The number should be a timeserial value, not an hour. Excel treats 1 as 24 hours, therefore 1 hour is 1/24. The range in O14:AG14 will not work as is.

 

[iliace]

iliace, that formula, after adjusting cell numbers to fit my file, gave me a #name? error.

I have it setup like this now...

"=SUMPRODUCT(--($E$5:$E$35<=P24),--($E$6:$E$36>=P24),--ISODD(ROW($E$5:$E$35)),--ISEVEN(ROW($E$6:$E$36)))"

Where E5 is the First Time In and E35 is the last Time In, E6 is the first Time Out and E36 is the last Time Out. P24 is where I have the time I'm looking for.

any ideas?

Yeah,

I did this in Excel 2007, which has the ISEVEN and ISODD functions; in previous versions, you have to install analysis toolpak to get them. This will work:

=SUMPRODUCT(--($E$5:$E$35<=P24),--($E$6:$E$36>=P24),MOD(ROW($E$5:$E$35),2))

However, looking at your spreadsheet, I don't see where the hour value is in P24. The number should be a timeserial value, not an hour. Excel treats 1 as 24 hours, therefore 1 hour is 1/24. The range in O14:AG14 will not work as is.