| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to reference last non-blank cell value from a column cells? | Excel Worksheet Functions | |||
| Count no. of nonblank cells in one column based on criteria of ano | Excel Discussion (Misc queries) | |||
| count non blank cells with criteria | Excel Discussion (Misc queries) | |||
| COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN | Excel Worksheet Functions | |||
| count non blank cells which meet criteria in another column | Excel Worksheet Functions | |||
Linear Mode
