That's always a good thing to think of. Another thing, especially if lots of
dates or comparisons are going to take place, is speed. SUMPRODUCT() is a
pretty slow operation, and so the formula I put up earlier might offer better
performance IF there are lots of comparisons to be made. But for relatively
few comparisons, you would probably be hard pressed to measure any difference
in how long it took to get the results.
I personally prefer the SUMPRODUCT() solution over the brute force one I put
up simply because it is easier to understand and maintain. Most people get
confused to some degree when dealing with lots of logic statements (OR/AND)
in formulas, so that makes them more difficult to manage and maintain in the
long run.

"Ron Rosenfeld" wrote:

On Thu, 28 Aug 2008 17:51:01 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

The limit in 2003 is 1024 characters in a formula, shouldn't be any problem
with the length of the formula you provided. I was thinking of a
SUMPRODUCT() formula myself, but I couldn't get it worked out in my head this
evening, so I went with the brute force attack.

I thought it was something like that.

I wasn't concerned about the length of the formula I supplied -- only about
what would happen if he needed to extend it to test his input against more date
ranges than the three he shows.

Each SUMPRODUCT line is about 92 characters, so, in Excel 2003, he could get
about 11 date ranges to test against without having to go to a second cell.
--ron