| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting function but not double counting duplicates | Excel Worksheet Functions | |||
| Counting | Excel Discussion (Misc queries) | |||
| Help Counting | Excel Worksheet Functions | |||
| Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
| Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
Linear Mode
