I think you want to use the sumproduct function. Look here for some samples
of how you may do this:
http://www.contextures.com/xlFunctio...tml#SumProduct
http://www.ozgrid.com/News/apr-2005.htm

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Hii Sing Chung" wrote:

Hi,

I need help to code VBA in Excel to:
Count the number of e-mail with subject beginning with "Virus Alert:
Detected Not Removed" on a specified date, the date is the value of cell in
the date column. Sample data:
A B
1 Date Alert Mails
2 03 Aug 2009 3
3 04 Aug 2009
4 05 Aug 2009

At the worksheet activate event I want to let the code check if any there is
empty cell in the "Alert Mails" column for which the corresponding date
value in "date" column is today or older. If there is (in the sample above,
the cell B3 is empty and meets the condition that it corresponds to today or
older), go to Outlook folder "Virus Alerts" and count the number of e-mails
with the subject beginning with "Virus Alert: Detected Not Removed" and the
send date of the e-mail = value of A3. The result of the count should then
be entered into B3. It should then continue to check for other cells in
"Alert Mails" column and do the counting of e-mails until corresponding cell
in column date is greater than today.

Any help is very much appreciated.