Unique number counting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Untested
=SUMPRODUCT(--(A2:A1000>=1),--(E2:E1000<>""),--(1/COUNTIF(E2:E1000,E2:E1000)))
try it with a small data set
best wishes
 
see first of TWO previous posts

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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.
 
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
 
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
 
Back
Top