Counting

  • Thread starter Thread starter ronbwa
  • Start date Start date
R

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?
 
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.
 
Does this do what you want?

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

Rick
 
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<>""))
 
May I ask what <>"" refers to?

ronbwa said:
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<>""))
Click to expand...
 
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
 
Back
Top