Can someone please help me with writing a function? I would like to count
the number of cells containing a date given some criteria. The example would
be: If Column A is not blank, and Column B contains the word "Open", and
Column C contains a date, then count the number of cells in column C that has
a date.
Thank you

If you use Excel 2007, you can use the COUNTIFS function.

Otherwise, you can use this formula:

=SUMPRODUCT(--(A:A<""), --(B:B="Open"), --(C:C<0))

I'm assuming that the only things in the column C are dates, or they would
be blank.

(Note: I don't have excel with me right now, so I didn't get a chance to
test the formula above. Sorry about that.)

--
Location: Sydney
Occupation: Actuarial Consultant (Project Finance/Financial Modeling)


"Glenn" wrote:

Can someone please help me with writing a function? I would like to count
the number of cells containing a date given some criteria. The example would
be: If Column A is not blank, and Column B contains the word "Open", and
Column C contains a date, then count the number of cells in column C that has
a date.
Thank you

"Harimau" wrote in message
...
If you use Excel 2007, you can use the COUNTIFS function.

Otherwise, you can use this formula:

=SUMPRODUCT(--(A:A<""), --(B:B="Open"), --(C:C<0))


No you can't, SUMPRODUCT prior to Excel 2007 doesn't work with complete
columns, you have to specify a range.

Harimau,

Thank you for you quick and correct response. I applied your logic to my
spreadsheet, which was much more advanced than my example, and it worked! I
did note that you did not specify a range for your cells and I knew that I
needed to do that with my version of excel.
Bob: Thanks for confirming that for me.

Thanks again!

"Harimau" wrote:

If you use Excel 2007, you can use the COUNTIFS function.

Otherwise, you can use this formula:

=SUMPRODUCT(--(A:A<""), --(B:B="Open"), --(C:C<0))

I'm assuming that the only things in the column C are dates, or they would
be blank.

(Note: I don't have excel with me right now, so I didn't get a chance to
test the formula above. Sorry about that.)

--
Location: Sydney
Occupation: Actuarial Consultant (Project Finance/Financial Modeling)


"Glenn" wrote:

Can someone please help me with writing a function? I would like to count
the number of cells containing a date given some criteria. The example would
be: If Column A is not blank, and Column B contains the word "Open", and
Column C contains a date, then count the number of cells in column C that has
a date.
Thank you