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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top