Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique number counting
I have used a formula from an earlier discussion to count the number of
unique number appearing between E2:E1000. =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) Column E being house numbers. I would now like to count how many entries between A2:A1000 =1. Column A is the number of days a repair is outstanding. The result would show the number of houses that had repairs outstanding. Each house can have a number of repairs each with a different number of days outstanding. Can anyone help me with this please? Many thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique number counting
Untested
=SUMPRODUCT(--(A2:A1000=1),--(E2:E1000<""),--(1/COUNTIF(E2:E1000,E2:E1000))) try it with a small data set best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DianeandChipps" wrote in message ... I have used a formula from an earlier discussion to count the number of unique number appearing between E2:E1000. =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) Column E being house numbers. I would now like to count how many entries between A2:A1000 =1. Column A is the number of days a repair is outstanding. The result would show the number of houses that had repairs outstanding. Each house can have a number of repairs each with a different number of days outstanding. Can anyone help me with this please? Many thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique number counting
see first of TWO previous posts
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "DianeandChipps" wrote in message ... I have used a formula from an earlier discussion to count the number of unique number appearing between E2:E1000. =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) Column E being house numbers. I would now like to count how many entries between A2:A1000 =1. Column A is the number of days a repair is outstanding. The result would show the number of houses that had repairs outstanding. Each house can have a number of repairs each with a different number of days outstanding. Can anyone help me with this please? Many thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique number counting
Thanks for your help, it works great.
Is there any way to vary the range as there may be a different number of rows each time so I avoid error messages on empty cells? What does the -- mean? I haven't come across this before. "Bernard Liengme" wrote: Untested =SUMPRODUCT(--(A2:A1000=1),--(E2:E1000<""),--(1/COUNTIF(E2:E1000,E2:E1000))) try it with a small data set best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DianeandChipps" wrote in message ... I have used a formula from an earlier discussion to count the number of unique number appearing between E2:E1000. =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) Column E being house numbers. I would now like to count how many entries between A2:A1000 =1. Column A is the number of days a repair is outstanding. The result would show the number of houses that had repairs outstanding. Each house can have a number of repairs each with a different number of days outstanding. Can anyone help me with this please? Many thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique number counting
Thanks very much, it works great. What does -- mean, I haven't come across
this before. Is there any way to vary the the cell range as there may be less than 1000 rows so I avoid the #DIV/0! error message? Many thanks "Bernard Liengme" wrote: Untested =SUMPRODUCT(--(A2:A1000=1),--(E2:E1000<""),--(1/COUNTIF(E2:E1000,E2:E1000))) try it with a small data set best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DianeandChipps" wrote in message ... I have used a formula from an earlier discussion to count the number of unique number appearing between E2:E1000. =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) Column E being house numbers. I would now like to count how many entries between A2:A1000 =1. Column A is the number of days a repair is outstanding. The result would show the number of houses that had repairs outstanding. Each house can have a number of repairs each with a different number of days outstanding. Can anyone help me with this please? Many thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique number counting
Thanks Bob but this is only counting the number of entries in column A =1.
I was needing use this answer to count the number of houses (column E) which had an outstanding repair =1 day. Bernard has answered this but I still had a few queries which I have posted. Many thanks for your time. Diane "Bob Phillips" wrote: see first of TWO previous posts -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "DianeandChipps" wrote in message ... I have used a formula from an earlier discussion to count the number of unique number appearing between E2:E1000. =SUMPRODUCT((E2:E1000<"")/COUNTIF(E2:E1000,E2:E1000&"")) Column E being house numbers. I would now like to count how many entries between A2:A1000 =1. Column A is the number of days a repair is outstanding. The result would show the number of houses that had repairs outstanding. Each house can have a number of repairs each with a different number of days outstanding. Can anyone help me with this please? Many thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique number counting
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
-- David Biddulph "DianeandChipps" wrote in message ... Thanks very much, it works great. What does -- mean, I haven't come across this before. .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Filter unique random number | Excel Worksheet Functions | |||
Match two tables using unique ID number | Excel Discussion (Misc queries) | |||
Counting the number of cells meeting conditional formating criteria | Excel Worksheet Functions | |||
Counting number of grades in a row | Excel Worksheet Functions |