Counting

[ronbwa]

On my worksheet Column A contains dates 1/1/08 thru 12/31/08 (A2:A367)
Column B contains entries for dates in Column A. When I select a certain
date I want to be able to count the cells in Column B that contains a number
up to and including the date selected. Can anyone help?

 

[Sean Timmons]

sounds like you want

=sumproduct(--(A2:A367<=date selected),B2:B367)

 

[ronbwa]

I just want to count the number of cells that have a number in them in Column
B not the sum of the contents of the cells with respect to the date selected.

 

[Rick Rothstein \(MVP - VB\)]

Does this do what you want?

=SUMPRODUCT((A2:A367<=date selected)*(A2:A367<>""))

Rick

 

[ronbwa]

Thank you, thank you, thank you! Just had to alter a bit, but got what I wanted

=SUMPRODUCT((Sheet1!A2:A367<=D6)*(Sheet1!B2:B367<>""))

 

[ronbwa]

May I ask what <>"" refers to?

Thank you, thank you, thank you! Just had to alter a bit, but got what I wanted

=SUMPRODUCT((Sheet1!A2:A367<=D6)*(Sheet1!B2:B367<>""))

 

[Sean Timmons]

Is not blank (less than or greater than null)

May I ask what <>"" refers to?

 

[ronbwa]

Thank you

Is not blank (less than or greater than null)

 

[Rick Rothstein \(MVP - VB\)]

It means "not equal to". In a numerical comparison, blanks are treated as
zero and, if you had any blank values in Column A, would affect the count
being performed. By the way, if you do NOT (and NEVER will) have any blank
cells in your range, you can remove that term and just use this...

=SUMPRODUCT(--(Sheet1!A2:A367<=D6))

where the double unary (--) symbol is a method to convert Boolean
(TRUE/FALSE) values to numerical values (1/0) so that an addition can be
performed with them.

Rick