| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique counting formula
Thanks to an earlier discussion I have been able to use the formula
=SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) to count how many different numbers appear between cells E2 and E1000. Column E is house numbers. I would now like to be able to count how many entries in cells A2:A1000 are =1. Column A is the number of days that a repair is outstanding. The result would be the number of houses with a repair outstanding by 1 day or more. Can anyone make sense of what I have said and help? Many thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting formula
=COUNTIF(A:A,"=1")
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "DianeandChipps" wrote in message ... Thanks to an earlier discussion I have been able to use the formula =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) to count how many different numbers appear between cells E2 and E1000. Column E is house numbers. I would now like to be able to count how many entries in cells A2:A1000 are =1. Column A is the number of days that a repair is outstanding. The result would be the number of houses with a repair outstanding by 1 day or more. Can anyone make sense of what I have said and help? Many thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting formula
Try this:
=COUNTIF(A2:A1000,"=1") Biff "DianeandChipps" wrote in message ... Thanks to an earlier discussion I have been able to use the formula =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) to count how many different numbers appear between cells E2 and E1000. Column E is house numbers. I would now like to be able to count how many entries in cells A2:A1000 are =1. Column A is the number of days that a repair is outstanding. The result would be the number of houses with a repair outstanding by 1 day or more. Can anyone make sense of what I have said and help? Many thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting formula
Thanks for your help, sorry for the duplicate entries but I was getting error
messages and didn't think it had been posted. Bernard Liengme has sent me a formula that works but causes an error if there is an empty cell. Thanks very much again. "Biff" wrote: Try this: =COUNTIF(A2:A1000,"=1") Biff "DianeandChipps" wrote in message ... Thanks to an earlier discussion I have been able to use the formula =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) to count how many different numbers appear between cells E2 and E1000. Column E is house numbers. I would now like to be able to count how many entries in cells A2:A1000 are =1. Column A is the number of days that a repair is outstanding. The result would be the number of houses with a repair outstanding by 1 day or more. Can anyone make sense of what I have said and help? Many thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting formula
What are you trying to do?
Count *any* cell that is =1 in A2:A1000 or count only the unique house numbers in E2:E1000 that are =1 in A2:A1000 ? Biff "DianeandChipps" wrote in message ... Thanks for your help, sorry for the duplicate entries but I was getting error messages and didn't think it had been posted. Bernard Liengme has sent me a formula that works but causes an error if there is an empty cell. Thanks very much again. "Biff" wrote: Try this: =COUNTIF(A2:A1000,"=1") Biff "DianeandChipps" wrote in message ... Thanks to an earlier discussion I have been able to use the formula =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) to count how many different numbers appear between cells E2 and E1000. Column E is house numbers. I would now like to be able to count how many entries in cells A2:A1000 are =1. Column A is the number of days that a repair is outstanding. The result would be the number of houses with a repair outstanding by 1 day or more. Can anyone make sense of what I have said and help? Many thanks. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting formula
I am trying to count only the unique house numbers in E2:E1000 that are =1
in A2:A1000 The number of rows many vary. Many thanks Diane "Biff" wrote: What are you trying to do? Count *any* cell that is =1 in A2:A1000 or count only the unique house numbers in E2:E1000 that are =1 in A2:A1000 ? Biff "DianeandChipps" wrote in message ... Thanks for your help, sorry for the duplicate entries but I was getting error messages and didn't think it had been posted. Bernard Liengme has sent me a formula that works but causes an error if there is an empty cell. Thanks very much again. "Biff" wrote: Try this: =COUNTIF(A2:A1000,"=1") Biff "DianeandChipps" wrote in message ... Thanks to an earlier discussion I have been able to use the formula =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) to count how many different numbers appear between cells E2 and E1000. Column E is house numbers. I would now like to be able to count how many entries in cells A2:A1000 are =1. Column A is the number of days that a repair is outstanding. The result would be the number of houses with a repair outstanding by 1 day or more. Can anyone make sense of what I have said and help? Many thanks. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting formula
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =SUM(N(FREQUENCY(IF((A2:A1000=1)*(E2:E1000<""),M ATCH(E2:E1000&"",E2:E1000&"",0)),MATCH(E2:E1000&"" ,E2:E1000&"",0))0)) Biff "DianeandChipps" wrote in message ... I am trying to count only the unique house numbers in E2:E1000 that are =1 in A2:A1000 The number of rows many vary. Many thanks Diane "Biff" wrote: What are you trying to do? Count *any* cell that is =1 in A2:A1000 or count only the unique house numbers in E2:E1000 that are =1 in A2:A1000 ? Biff "DianeandChipps" wrote in message ... Thanks for your help, sorry for the duplicate entries but I was getting error messages and didn't think it had been posted. Bernard Liengme has sent me a formula that works but causes an error if there is an empty cell. Thanks very much again. "Biff" wrote: Try this: =COUNTIF(A2:A1000,"=1") Biff "DianeandChipps" wrote in message ... Thanks to an earlier discussion I have been able to use the formula =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) to count how many different numbers appear between cells E2 and E1000. Column E is house numbers. I would now like to be able to count how many entries in cells A2:A1000 are =1. Column A is the number of days that a repair is outstanding. The result would be the number of houses with a repair outstanding by 1 day or more. Can anyone make sense of what I have said and help? Many thanks. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting formula
Excellant, thanks very much
"Biff" wrote: Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =SUM(N(FREQUENCY(IF((A2:A1000=1)*(E2:E1000<""),M ATCH(E2:E1000&"",E2:E1000&"",0)),MATCH(E2:E1000&"" ,E2:E1000&"",0))0)) Biff "DianeandChipps" wrote in message ... I am trying to count only the unique house numbers in E2:E1000 that are =1 in A2:A1000 The number of rows many vary. Many thanks Diane "Biff" wrote: What are you trying to do? Count *any* cell that is =1 in A2:A1000 or count only the unique house numbers in E2:E1000 that are =1 in A2:A1000 ? Biff "DianeandChipps" wrote in message ... Thanks for your help, sorry for the duplicate entries but I was getting error messages and didn't think it had been posted. Bernard Liengme has sent me a formula that works but causes an error if there is an empty cell. Thanks very much again. "Biff" wrote: Try this: =COUNTIF(A2:A1000,"=1") Biff "DianeandChipps" wrote in message ... Thanks to an earlier discussion I have been able to use the formula =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) to count how many different numbers appear between cells E2 and E1000. Column E is house numbers. I would now like to be able to count how many entries in cells A2:A1000 are =1. Column A is the number of days that a repair is outstanding. The result would be the number of houses with a repair outstanding by 1 day or more. Can anyone make sense of what I have said and help? Many thanks. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique counting formula
You're welcome. Thanks for the feedback!
Biff "DianeandChipps" wrote in message ... Excellant, thanks very much "Biff" wrote: Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =SUM(N(FREQUENCY(IF((A2:A1000=1)*(E2:E1000<""),M ATCH(E2:E1000&"",E2:E1000&"",0)),MATCH(E2:E1000&"" ,E2:E1000&"",0))0)) Biff "DianeandChipps" wrote in message ... I am trying to count only the unique house numbers in E2:E1000 that are =1 in A2:A1000 The number of rows many vary. Many thanks Diane "Biff" wrote: What are you trying to do? Count *any* cell that is =1 in A2:A1000 or count only the unique house numbers in E2:E1000 that are =1 in A2:A1000 ? Biff "DianeandChipps" wrote in message ... Thanks for your help, sorry for the duplicate entries but I was getting error messages and didn't think it had been posted. Bernard Liengme has sent me a formula that works but causes an error if there is an empty cell. Thanks very much again. "Biff" wrote: Try this: =COUNTIF(A2:A1000,"=1") Biff "DianeandChipps" wrote in message ... Thanks to an earlier discussion I have been able to use the formula =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) to count how many different numbers appear between cells E2 and E1000. Column E is house numbers. I would now like to be able to count how many entries in cells A2:A1000 are =1. Column A is the number of days that a repair is outstanding. The result would be the number of houses with a repair outstanding by 1 day or more. Can anyone make sense of what I have said and help? Many thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting Unique Values | Excel Worksheet Functions | |||
| Match then lookup | Excel Worksheet Functions | |||
| Counting unique records with additional criteria | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Counting Unique Records with multiple conditions | Excel Worksheet Functions | |||
Linear Mode
