Lookup date range row A and count if leave in same column on row B

[Pappa]

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.

 

[Ms-Exl-Learner]

Just try this...

=COUNTIF(B2:F2,"L")

Change the Cell reference B2:F2 to your desired range.

If this post helps, Click Yes!

 

[Pappa]

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

 

[Pappa]

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.

 

[Max]

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]

Thanks Max. Your solution not only works but is concise...simple yet effective.
I really appreciate the help.

 

[Max]

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
--