Hi!

Try this little exercise:

Create this small table:

...........A...................B
1.......Eng..........4/27/2006
2.......Const.......3/30/2006
3.......Eng...........Pending
4.......Trans........Pending
5.......Eng...........(empty)

Now lets break down the formula into its individual parts:

=SUMPRODUCT(--(A1:A5="Eng"),--(ISNUMBER(B1:B5)))

Enter this formula in D1 and copy down to D5:

=--(A1="Eng")

Enter this formula in E1 and copy down to E5:

=--ISNUMBER(B1)

Enter this formula F1 and copy down to F5:

=D1*E1

And finally, enter this formula in G1:

=SUM(F1:F5)

That's what's happening with this formula:

=SUMPRODUCT(--(A1:A5="Eng"),--(ISNUMBER(B1:B5)))

Dates are really just numbers that are formatted to look like dates. So, to
test if a date is present all you need to do is test the cell to see if it
contains a number, thus ISNUMBER.

The "--" double unary is used to convert boolean values: TRUE or FALSE to
numeric values 1 or 0:

(A1="Eng") will return either TRUE or FALSE

--(A1="Eng") will return either 1 or 0

Biff

"HDV" wrote in message
...

It works - how does it work though I'm bamboozled. - thanks Biff.

HDV :)


--
HDV
------------------------------------------------------------------------
HDV's Profile:
http://www.excelforum.com/member.php...o&userid=26299
View this thread: http://www.excelforum.com/showthread...hreadid=536989