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.

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.

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.

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.

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.

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.

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.
....