Unique number counting

[DianeandChipps]

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.

 

[Bernard Liengme]

Untested
=SUMPRODUCT(--(A2:A1000>=1),--(E2:E1000<>""),--(1/COUNTIF(E2:E1000,E2:E1000)))
try it with a small data set
best wishes

 

[Bob Phillips]

see first of TWO previous posts

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[DianeandChipps]

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.

 

[DianeandChipps]

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

 

[DianeandChipps]

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

 

[David Biddulph]

http://www.mcgimpsey.com/excel/formulae/doubleneg.html
--
David Biddulph

Thanks very much, it works great. What does -- mean, I haven't come
across
this before.

....