Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup date range row A and count if leave in same column on row B
Hi
I am attempting in Excel 2003 to lookup a date range in row A and then count leave booked in row B. e.g. 01 02 03 04 05 TIM L L L KIM L L Count leave for Tim period 02 to 04 inclusive. (L's for leave could be replaced with numeric) Please note that 1. The date range would be entered in two seperate cells not directly into the function. 2. The date period in row A would be running for a year so any function would need to search the entire period. -- Cheers Pappa Smurf |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup date range row A and count if leave in same column on row B
Just try this...
=COUNTIF(B2:F2,"L") Change the Cell reference B2:F2 to your desired range. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Pappa" wrote: Hi I am attempting in Excel 2003 to lookup a date range in row A and then count leave booked in row B. e.g. 01 02 03 04 05 TIM L L L KIM L L Count leave for Tim period 02 to 04 inclusive. (L's for leave could be replaced with numeric) Please note that 1. The date range would be entered in two seperate cells not directly into the function. 2. The date period in row A would be running for a year so any function would need to search the entire period. -- Cheers Pappa Smurf |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup date range row A and count if leave in same column on r
Sorry but I mucked up what I meant was row 1 and 2 not A and B.
Ms-Exl-Learner- This will count the L without any reference to the date in row 1. I want to count for L on row 2 only if it falls within a specified date range on row 1 -- Cheers Pappa Smurf "Ms-Exl-Learner" wrote: Just try this... =COUNTIF(B2:F2,"L") Change the Cell reference B2:F2 to your desired range. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Pappa" wrote: Hi I am attempting in Excel 2003 to lookup a date range in row A and then count leave booked in row B. e.g. 01 02 03 04 05 TIM L L L KIM L L Count leave for Tim period 02 to 04 inclusive. (L's for leave could be replaced with numeric) Please note that 1. The date range would be entered in two seperate cells not directly into the function. 2. The date period in row A would be running for a year so any function would need to search the entire period. -- Cheers Pappa Smurf |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup date range row 1 and count if leave in same column on row 2
Sorry I mucked up and meant row 1 and 2 not A & B.
Ms-Exl-Learner - The count you suggested will count the L's without reference to the specified date range. -- Cheers Pappa Smurf "Pappa" wrote: Hi I am attempting in Excel 2003 to lookup a date range in row 1 and then count leave booked in row 2. e.g. 01 02 03 04 05 TIM L L L KIM L L Count leave for Tim period 02 to 04 inclusive. (L's for leave could be replaced with numeric) Please note that 1. The date range would be entered in two seperate cells not directly into the function. 2. The date period in row A would be running for a year so any function would need to search the entire period. -- Cheers Pappa Smurf |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup date range row 1 and count if leave in same column on r
Real dates are assumed in B1:Z1 with corresponding data ("L"s, etc) in B2:Z2
down Date range desired: Startdate is specified in AA1, Enddate in AA2 (these 2 dates must be real dates) In AA2: =SUMPRODUCT(($B$1:$Z$1=$AA$1)*($B$1:$Z$1<=$AB$1)* (B2:Z2="L")) Copy down to return the required counts of "L" If data within B2:Z2 down are numbers (instead of the letter L) and you want to sum up these numbers instead for the specified date range, use in AA2, copied down: =SUMPRODUCT(($B$1:$Z$1=$AA$1)*($B$1:$Z$1<=$AB$1), B2:Z2) Above any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Pappa" wrote: Sorry I mucked up and meant row 1 and 2 not A & B. Ms-Exl-Learner - The count you suggested will count the L's without reference to the specified date range |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup date range row 1 and count if leave in same column on r
Thanks Max. Your solution not only works but is concise...simple yet effective.
I really appreciate the help. -- Cheers Pappa Smurf "Max" wrote: Real dates are assumed in B1:Z1 with corresponding data ("L"s, etc) in B2:Z2 down Date range desired: Startdate is specified in AA1, Enddate in AA2 (these 2 dates must be real dates) In AA2: =SUMPRODUCT(($B$1:$Z$1=$AA$1)*($B$1:$Z$1<=$AB$1)* (B2:Z2="L")) Copy down to return the required counts of "L" If data within B2:Z2 down are numbers (instead of the letter L) and you want to sum up these numbers instead for the specified date range, use in AA2, copied down: =SUMPRODUCT(($B$1:$Z$1=$AA$1)*($B$1:$Z$1<=$AB$1), B2:Z2) Above any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Pappa" wrote: Sorry I mucked up and meant row 1 and 2 not A & B. Ms-Exl-Learner - The count you suggested will count the L's without reference to the specified date range |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup date range row 1 and count if leave in same column on r
Welcome, good to hear
Had a typo earlier for this line: .. Enddate in AA2 It should read: .. Enddate in AB1 but I think you caught that <g -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik -- "Pappa" wrote: Thanks Max. Your solution not only works but is concise...simple yet effective. I really appreciate the help. -- Cheers Pappa Smurf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values in 1 Column based on Date Range in another Column | Excel Worksheet Functions | |||
Lookup / return latest date in range AND value of 1 column to the | Excel Worksheet Functions | |||
2 column lookup - match to date range | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel |