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

  • Thread starter Thread starter michael.engelhardt
  • Start date Start date
M

michael.engelhardt

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.
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Try this:

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

--
Biff
Microsoft Excel MVP








- Show quoted text -
Click to expand...

Thanks.
Works like a charm.
Mike
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP






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

- Show quoted text -
Click to expand...

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?
 
The cell 'Invoice List 2009'!C302 is August 1, 2009
in the proper date format.
Click to expand...

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 -
Click to expand...

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?
 
Back
Top