|
Counting
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? |
Counting
sounds like you want
=sumproduct(--(A2:A367<=date selected),B2:B367) "ronbwa" wrote: 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? |
Counting
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. "Sean Timmons" wrote: sounds like you want =sumproduct(--(A2:A367<=date selected),B2:B367) "ronbwa" wrote: 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? |
Counting
Does this do what you want?
=SUMPRODUCT((A2:A367<=date selected)*(A2:A367<"")) Rick "ronbwa" wrote in message ... 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. "Sean Timmons" wrote: sounds like you want =sumproduct(--(A2:A367<=date selected),B2:B367) "ronbwa" wrote: 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? |
Counting
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< "")) "Rick Rothstein (MVP - VB)" wrote: Does this do what you want? =SUMPRODUCT((A2:A367<=date selected)*(A2:A367<"")) Rick "ronbwa" wrote in message ... 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. "Sean Timmons" wrote: sounds like you want =sumproduct(--(A2:A367<=date selected),B2:B367) "ronbwa" wrote: 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? |
Counting
May I ask what <"" refers to?
"ronbwa" wrote: 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< "")) "Rick Rothstein (MVP - VB)" wrote: Does this do what you want? =SUMPRODUCT((A2:A367<=date selected)*(A2:A367<"")) Rick "ronbwa" wrote in message ... 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. "Sean Timmons" wrote: sounds like you want =sumproduct(--(A2:A367<=date selected),B2:B367) "ronbwa" wrote: 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? |
Counting
Is not blank (less than or greater than null)
"ronbwa" wrote: May I ask what <"" refers to? "ronbwa" wrote: 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< "")) "Rick Rothstein (MVP - VB)" wrote: Does this do what you want? =SUMPRODUCT((A2:A367<=date selected)*(A2:A367<"")) Rick "ronbwa" wrote in message ... 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. "Sean Timmons" wrote: sounds like you want =sumproduct(--(A2:A367<=date selected),B2:B367) "ronbwa" wrote: 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? |
Counting
Thank you
"Sean Timmons" wrote: Is not blank (less than or greater than null) "ronbwa" wrote: May I ask what <"" refers to? "ronbwa" wrote: 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< "")) "Rick Rothstein (MVP - VB)" wrote: Does this do what you want? =SUMPRODUCT((A2:A367<=date selected)*(A2:A367<"")) Rick "ronbwa" wrote in message ... 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. "Sean Timmons" wrote: sounds like you want =sumproduct(--(A2:A367<=date selected),B2:B367) "ronbwa" wrote: 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? |
Counting
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 "ronbwa" wrote in message ... May I ask what <"" refers to? "ronbwa" wrote: 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< "")) "Rick Rothstein (MVP - VB)" wrote: Does this do what you want? =SUMPRODUCT((A2:A367<=date selected)*(A2:A367<"")) Rick "ronbwa" wrote in message ... 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. "Sean Timmons" wrote: sounds like you want =sumproduct(--(A2:A367<=date selected),B2:B367) "ronbwa" wrote: 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? |
| All times are GMT +1. The time now is 09:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com