Count Non-Blank Cells in Column If a Reference Criteria Met.
 

Hi.
Cells A4:A1000 contain Dates (1/1/08, 2/1/08, 3/1/09...)
Cells D4:D1000 contain Data (22, 143, 46,...)
Cell A1 Will contain my search criteria (2009...)
In Cell D1 I need to have a count of Non-Blank Cells D4:D1000 that are
in the year of Cell A1

A B C D
1 2009 1
2
3
4 1/1/08 22
5 2/1/08 143
6 3/1/09 46

Thank you for any help.
M. E.

Count Non-Blank Cells in Column If a Reference Criteria Met.
 

Try this:

=SUMPRODUCT(--(YEAR(A4:A1000)=A1),--(D4:D1000<""))

--
Biff
Microsoft Excel MVP


wrote in message
...
Hi.
Cells A4:A1000 contain Dates (1/1/08, 2/1/08, 3/1/09...)
Cells D4:D1000 contain Data (22, 143, 46,...)
Cell A1 Will contain my search criteria (2009...)
In Cell D1 I need to have a count of Non-Blank Cells D4:D1000 that are
in the year of Cell A1

A B C D
1 2009 1
2
3
4 1/1/08 22
5 2/1/08 143
6 3/1/09 46

Thank you for any help.
M. E.

Count Non-Blank Cells in Column If a Reference Criteria Met.
 

On Jul 15, 8:38*pm, "T. Valko" wrote:
Try this:

=SUMPRODUCT(--(YEAR(A4:A1000)=A1),--(D4:D1000<""))

--
Biff
Microsoft Excel MVP

wrote in message

...



Hi.
Cells A4:A1000 contain Dates (1/1/08, 2/1/08, 3/1/09...)
Cells D4:D1000 contain Data *(22, 143, 46,...)
Cell A1 Will contain my search criteria (2009...)
In Cell D1 I need to have a count of Non-Blank Cells D4:D1000 that are
in the year of Cell A1

* * * * *A * * * * B * * * * * * *C * * * * * *D
1 * * *2009 * * * * * * * * * * * * * * * * * * 1
2
3
4 * 1/1/08 * * * * * * * * * * * * * * * * * * *22
5 * 2/1/08 * * * * * * * * * * * * * * * * * *143
6 * 3/1/09 * * * * * * * * * * * * * * * * * * 46

Thank you for any help.
M. E.- Hide quoted text -

- Show quoted text -

Thanks.
Works like a charm.
Mike

Count Non-Blank Cells in Column If a Reference Criteria Met.
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
On Jul 15, 8:38 pm, "T. Valko" wrote:
Try this:

=SUMPRODUCT(--(YEAR(A4:A1000)=A1),--(D4:D1000<""))

--
Biff
Microsoft Excel MVP

wrote in message

...



Hi.
Cells A4:A1000 contain Dates (1/1/08, 2/1/08, 3/1/09...)
Cells D4:D1000 contain Data (22, 143, 46,...)
Cell A1 Will contain my search criteria (2009...)
In Cell D1 I need to have a count of Non-Blank Cells D4:D1000 that are
in the year of Cell A1

A B C D
1 2009 1
2
3
4 1/1/08 22
5 2/1/08 143
6 3/1/09 46

Thank you for any help.
M. E.- Hide quoted text -

- Show quoted text -

Thanks.
Works like a charm.
Mike

Count Non-Blank Cells in Column If a Reference Criteria Met.
 

On Jul 15, 11:29*pm, "T. Valko" wrote:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Mike" wrote in message

...
On Jul 15, 8:38 pm, "T. Valko" wrote:





Try this:

=SUMPRODUCT(--(YEAR(A4:A1000)=A1),--(D4:D1000<""))

--
Biff
Microsoft Excel MVP

wrote in message

....

Hi.
Cells A4:A1000 contain Dates (1/1/08, 2/1/08, 3/1/09...)
Cells D4:D1000 contain Data (22, 143, 46,...)
Cell A1 Will contain my search criteria (2009...)
In Cell D1 I need to have a count of Non-Blank Cells D4:D1000 that are
in the year of Cell A1

A B C D
1 2009 1
2
3
4 1/1/08 22
5 2/1/08 143
6 3/1/09 46

Thank you for any help.
M. E.- Hide quoted text -

- Show quoted text -

Thanks.
Works like a charm.
Mike- Hide quoted text -

- Show quoted text -

How could you do the same thing but for months? The formula that I
used is =SUMPRODUCT(--(MONTH('Invoice List 2009'!C75:C420)='Invoice
List 2009'!C302),--('Invoice List 2009'!B75:B420<"")). Basically it's
the same idea that Mike has but I want the non blank cells in column B
counted if the month formula contains the month of August. The cell
'Invoice List 2009'!C302 is August 1, 2009 in the proper date format.

Any advice?

Count Non-Blank Cells in Column If a Reference Criteria Met.
 

The cell 'Invoice List 2009'!C302 is August 1, 2009
in the proper date format.

Try this:

=SUMPRODUCT(--(MONTH('Invoice List 2009'!C75:C420)=MONTH('Invoice
List 2009'!C302)),--('Invoice List 2009'!B75:B420<""))

--
Biff
Microsoft Excel MVP


"CLIWIL" wrote in message
...
On Jul 15, 11:29 pm, "T. Valko" wrote:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Mike" wrote in message

...
On Jul 15, 8:38 pm, "T. Valko" wrote:





Try this:

=SUMPRODUCT(--(YEAR(A4:A1000)=A1),--(D4:D1000<""))

--
Biff
Microsoft Excel MVP

wrote in message

...

Hi.
Cells A4:A1000 contain Dates (1/1/08, 2/1/08, 3/1/09...)
Cells D4:D1000 contain Data (22, 143, 46,...)
Cell A1 Will contain my search criteria (2009...)
In Cell D1 I need to have a count of Non-Blank Cells D4:D1000 that are
in the year of Cell A1

A B C D
1 2009 1
2
3
4 1/1/08 22
5 2/1/08 143
6 3/1/09 46

Thank you for any help.
M. E.- Hide quoted text -

- Show quoted text -

Thanks.
Works like a charm.
Mike- Hide quoted text -

- Show quoted text -

How could you do the same thing but for months? The formula that I
used is =SUMPRODUCT(--(MONTH('Invoice List 2009'!C75:C420)='Invoice
List 2009'!C302),--('Invoice List 2009'!B75:B420<"")). Basically it's
the same idea that Mike has but I want the non blank cells in column B
counted if the month formula contains the month of August. The cell
'Invoice List 2009'!C302 is August 1, 2009 in the proper date format.

Any advice?