Ranges and dates
 

Hello everyone.
I've been searching for a way to formulate the following:

If a A1 is found to be within a named range, and A1 < B1, I need to add 1
to C1, and if its not found within that named range, then add 0 to C1.

I've looked at it for so long that I'm crosseyed already. Help would be
welcomed and I'd be most grateful. Thanks.....Ben

Ranges and dates
 

clarify "If a A1 is found to be within a named range"
do you mean A1 is between to values or is part of a named range?

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"ben simpson" wrote in message
...
Hello everyone.
I've been searching for a way to formulate the following:

If a A1 is found to be within a named range, and A1 < B1, I need to add 1
to C1, and if its not found within that named range, then add 0 to C1.

I've looked at it for so long that I'm crosseyed already. Help would be
welcomed and I'd be most grateful. Thanks.....Ben

Ranges and dates
 

In A1 if the TODAY() (to avoid the volatile error).
The named range is a list of dates, (Holiday dates that are upcoming)
In A12:AH13 is the range of dates that I need to check against the named
range. It a work cycle.
A2 is 1 day past the end od the work cycle.

I need to check how many,if any, of the holiday dates fall within the
present work cycle, and place the number of matches that are found in a cell
to be added to keep a running total.

Hope this make sense. Thanks....Ben



"Bernard Liengme" wrote:

clarify "If a A1 is found to be within a named range"
do you mean A1 is between to values or is part of a named range?

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"ben simpson" wrote in message
...
Hello everyone.
I've been searching for a way to formulate the following:

If a A1 is found to be within a named range, and A1 < B1, I need to add 1
to C1, and if its not found within that named range, then add 0 to C1.

I've looked at it for so long that I'm crosseyed already. Help would be
welcomed and I'd be most grateful. Thanks.....Ben

Ranges and dates
 

If I understand correctly you want to count how many dates within your
range fall between A1 and A2. If so

=SUMPRODUCT(--(A12:AH13=A1),--(A12:AH13<=A2))

or

=COUNTIF(A12:AH13,"="&A1)-COUNTIF(A12:AH13,""&A2)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=522428

Ranges and dates
 

What I've been using is:
{=SUM(COUNTIF(Holidays,H12:AH13))}
This counts the number of times that the dates match within the work period.
What I was looking for is a way to populate the cell with a 1 on the date it
matches Ex: 1 holiday is added on 1/1/2006 for the new years holiday, and so
on, even if its in the middle of a work cycle. I have each of the actual
dates listed in the named range...Ben

"daddylonglegs" wrote:


If I understand correctly you want to count how many dates within your
range fall between A1 and A2. If so

=SUMPRODUCT(--(A12:AH13=A1),--(A12:AH13<=A2))

or

=COUNTIF(A12:AH13,"="&A1)-COUNTIF(A12:AH13,""&A2)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=522428

Ranges and dates
 

OK try this...

=SUMPRODUCT(--(H12:AH13<=TODAY()),--ISNUMBER(MATCH(H12:AH13,holidays,0)))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=522428

Ranges and dates
 

Perfect...Many, many thanks.....Works like a charm......Ben

"daddylonglegs" wrote:


OK try this...

=SUMPRODUCT(--(H12:AH13<=TODAY()),--ISNUMBER(MATCH(H12:AH13,holidays,0)))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=522428