Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a function
Hi
I am looking if there is a function that can retrive the number of cells with specific value in one column according to a specific value in another column. for example: A table with 2 columns, one for dates and the other is 0 or 1 values. I am wondering if there is a function that can tell me the number of cells with the value 1 in specific date. Thanks in advance Eli |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a function
Try this for a specific date and the number 1:
A1:A10 = Dates B1:10 = values =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(Values=1)) Change the date as needed. If you need to count for the date and the number 0: =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(ISNUMBER(Values)),--(Values=1)) Better to use cells to hold the criteria: D1 = some date like 1/1/2007 E1 = 1 =SUMPRODUCT(--(Dates=D1),--(ISNUMBER(Values)),--(Values=E1)) Biff "???" wrote in message ... Hi I am looking if there is a function that can retrive the number of cells with specific value in one column according to a specific value in another column. for example: A table with 2 columns, one for dates and the other is 0 or 1 values. I am wondering if there is a function that can tell me the number of cells with the value 1 in specific date. Thanks in advance Eli |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a function
ooops!
If you need to count for the date and the number 0: =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(ISNUMBER(Values)),--(Values=1)) The correct formula should be: =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(ISNUMBER(Values)),--(Values=0)) SUMPRODUCT will evaluate an empty cell as 0 so we need to add a test that makes sure there is actually a number in the Values cells. Biff "T. Valko" wrote in message ... Try this for a specific date and the number 1: A1:A10 = Dates B1:10 = values =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(Values=1)) Change the date as needed. If you need to count for the date and the number 0: =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(ISNUMBER(Values)),--(Values=1)) Better to use cells to hold the criteria: D1 = some date like 1/1/2007 E1 = 1 =SUMPRODUCT(--(Dates=D1),--(ISNUMBER(Values)),--(Values=E1)) Biff "???" wrote in message ... Hi I am looking if there is a function that can retrive the number of cells with specific value in one column according to a specific value in another column. for example: A table with 2 columns, one for dates and the other is 0 or 1 values. I am wondering if there is a function that can tell me the number of cells with the value 1 in specific date. Thanks in advance Eli |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a function
Hi
Assuming the data is in columns A and B, enter the date desired in C1 and in D1 enter =SUMPRODUCT(($A$1:$A$1000=$C$1)*($B$1:$B$1000=1)) Change ranges to suit, but ensure that they are of equal size. -- Regards Roger Govier "???" wrote in message ... Hi I am looking if there is a function that can retrive the number of cells with specific value in one column according to a specific value in another column. for example: A table with 2 columns, one for dates and the other is 0 or 1 values. I am wondering if there is a function that can tell me the number of cells with the value 1 in specific date. Thanks in advance Eli |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a function
I tried to do so but i got #NAME? error for the next data:
A B 01/01/2007 1 01/01/2007 0 01/01/2007 1 04/01/2007 0 05/01/2007 0 06/01/2007 1 07/01/2007 1 08/01/2007 1 09/01/2007 0 10/01/2007 0 (I used : =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(Values=1))) Eli "T. Valko" wrote: Try this for a specific date and the number 1: A1:A10 = Dates B1:10 = values =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(Values=1)) Change the date as needed. If you need to count for the date and the number 0: =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(ISNUMBER(Values)),--(Values=1)) Better to use cells to hold the criteria: D1 = some date like 1/1/2007 E1 = 1 =SUMPRODUCT(--(Dates=D1),--(ISNUMBER(Values)),--(Values=E1)) Biff "???" wrote in message ... Hi I am looking if there is a function that can retrive the number of cells with specific value in one column according to a specific value in another column. for example: A table with 2 columns, one for dates and the other is 0 or 1 values. I am wondering if there is a function that can tell me the number of cells with the value 1 in specific date. Thanks in advance Eli |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a function
Sorry, I forgot to define the names of the ranges.
Now it works fine. Thanks!!! "אלי" wrote: I tried to do so but i got #NAME? error for the next data: A B 01/01/2007 1 01/01/2007 0 01/01/2007 1 04/01/2007 0 05/01/2007 0 06/01/2007 1 07/01/2007 1 08/01/2007 1 09/01/2007 0 10/01/2007 0 (I used : =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(Values=1))) Eli "T. Valko" wrote: Try this for a specific date and the number 1: A1:A10 = Dates B1:10 = values =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(Values=1)) Change the date as needed. If you need to count for the date and the number 0: =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(ISNUMBER(Values)),--(Values=1)) Better to use cells to hold the criteria: D1 = some date like 1/1/2007 E1 = 1 =SUMPRODUCT(--(Dates=D1),--(ISNUMBER(Values)),--(Values=E1)) Biff "???" wrote in message ... Hi I am looking if there is a function that can retrive the number of cells with specific value in one column according to a specific value in another column. for example: A table with 2 columns, one for dates and the other is 0 or 1 values. I am wondering if there is a function that can tell me the number of cells with the value 1 in specific date. Thanks in advance Eli |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a function
Roger - Thanks!
You saved my next question "Roger Govier" wrote: Hi Assuming the data is in columns A and B, enter the date desired in C1 and in D1 enter =SUMPRODUCT(($A$1:$A$1000=$C$1)*($B$1:$B$1000=1)) Change ranges to suit, but ensure that they are of equal size. -- Regards Roger Govier "???" wrote in message ... Hi I am looking if there is a function that can retrive the number of cells with specific value in one column according to a specific value in another column. for example: A table with 2 columns, one for dates and the other is 0 or 1 values. I am wondering if there is a function that can tell me the number of cells with the value 1 in specific date. Thanks in advance Eli |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |