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

[michael.engelhardt]

Hi.
Cells A4:A1000 contain Dates (1/1/08, 2/1/08, 3/1/09...)
Cells D41000 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 D41000 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.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(YEAR(A4:A1000)=A1),--(D41000<>""))

 

[Mike]

Try this:

=SUMPRODUCT(--(YEAR(A4:A1000)=A1),--(D41000<>""))

--
Biff
Microsoft Excel MVP








- Show quoted text -

Thanks.
Works like a charm.
Mike

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Try this:

=SUMPRODUCT(--(YEAR(A4:A1000)=A1),--(D41000<>""))

--
Biff
Microsoft Excel MVP








- Show quoted text -

Thanks.
Works like a charm.
Mike

 

[CLIWIL]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP






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?

 

[T. Valko]

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP






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?