CountIF Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Glenn - 06 May 2008 13:10 GMT

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

Reply to this Message

Harimau - 06 May 2008 13:16 GMT

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.)

Signature

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

> 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

Reply to this Message

Bob Phillips - 06 May 2008 13:33 GMT

> 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.

Reply to this Message

Glenn - 06 May 2008 13:48 GMT

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!

> If you use Excel 2007, you can use the COUNTIFS function.
>
[quoted text clipped - 14 lines]
> > a date.
> > Thank you

Reply to this Message