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.