Unique counting formula

[Guest]

Thanks to an earlier discussion I have been able to use the formula
=SUMPRODUCT((E2:E1000<>"")/COUNTIF(E2:E1000,E2:E1000&"")) to count how many
different numbers appear between cells E2 and E1000. Column E is house
numbers.
I would now like to be able to count how many entries in cells A2:A1000 are

=1.

Column A is the number of days that a repair is outstanding.

The result would be the number of houses with a repair outstanding by 1 day
or more.

Can anyone make sense of what I have said and help?

Many thanks.

 

[Bob Phillips]

=COUNTIF(A:A,">=1")

--
HTH

Bob Phillips

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

 

[Biff]

Try this:

=COUNTIF(A2:A1000,">=1")

Biff

Thanks to an earlier discussion I have been able to use the formula
=SUMPRODUCT((E2:E1000<>"")/COUNTIF(E2:E1000,E2:E1000&"")) to count how
many
different numbers appear between cells E2 and E1000. Column E is house
numbers.
I would now like to be able to count how many entries in cells A2:A1000
are
Column A is the number of days that a repair is outstanding.

The result would be the number of houses with a repair outstanding by 1
day
or more.

Can anyone make sense of what I have said and help?

Many thanks.

 

[Guest]

Thanks for your help, sorry for the duplicate entries but I was getting error
messages and didn't think it had been posted.

Bernard Liengme has sent me a formula that works but causes an error if
there is an empty cell.

Thanks very much again.

 

[Biff]

What are you trying to do?

Count *any* cell that is >=1 in A2:A1000 or count only the unique house
numbers in E2:E1000 that are >=1 in A2:A1000 ?

Biff

 

[Guest]

I am trying to count only the unique house numbers in E2:E1000 that are >=1
in A2:A1000

The number of rows many vary.

Many thanks

Diane

 

[Biff]

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=SUM(N(FREQUENCY(IF((A2:A1000>=1)*(E2:E1000<>""),MATCH(E2:E1000&"",E2:E1000&"",0)),MATCH(E2:E1000&"",E2:E1000&"",0))>0))

Biff

 

[Guest]

Excellant, thanks very much

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=SUM(N(FREQUENCY(IF((A2:A1000>=1)*(E2:E1000<>""),MATCH(E2:E1000&"",E2:E1000&"",0)),MATCH(E2:E1000&"",E2:E1000&"",0))>0))

Biff

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Excellant, thanks very much