Unique counting formula
 

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.

Unique counting formula
 

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

--
HTH

Bob Phillips

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

"DianeandChipps" wrote in message
...
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.

Unique counting formula
 

Try this:

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

Biff

"DianeandChipps" wrote in message
...
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.

Unique counting formula
 

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" wrote:

Try this:

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

Biff

"DianeandChipps" wrote in message
...
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.

Unique counting formula
 

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

"DianeandChipps" wrote in message
...
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" wrote:

Try this:

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

Biff

"DianeandChipps" wrote in
message
...
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.

Unique counting formula
 

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" wrote:

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

"DianeandChipps" wrote in message
...
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" wrote:

Try this:

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

Biff

"DianeandChipps" wrote in
message
...
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.

Unique counting formula
 

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<""),M ATCH(E2:E1000&"",E2:E1000&"",0)),MATCH(E2:E1000&"" ,E2:E1000&"",0))0))

Biff

"DianeandChipps" wrote in message
...
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" wrote:

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

"DianeandChipps" wrote in
message
...
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" wrote:

Try this:

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

Biff

"DianeandChipps" wrote in
message
...
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.

Unique counting formula
 

Excellant, thanks very much

"Biff" wrote:

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<""),M ATCH(E2:E1000&"",E2:E1000&"",0)),MATCH(E2:E1000&"" ,E2:E1000&"",0))0))

Biff

"DianeandChipps" wrote in message
...
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" wrote:

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

"DianeandChipps" wrote in
message
...
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" wrote:

Try this:

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

Biff

"DianeandChipps" wrote in
message
...
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.

Unique counting formula
 

You're welcome. Thanks for the feedback!

Biff

"DianeandChipps" wrote in message
...
Excellant, thanks very much

"Biff" wrote:

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<""),M ATCH(E2:E1000&"",E2:E1000&"",0)),MATCH(E2:E1000&"" ,E2:E1000&"",0))0))

Biff

"DianeandChipps" wrote in
message
...
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" wrote:

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

"DianeandChipps" wrote in
message
...
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" wrote:

Try this:

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

Biff

"DianeandChipps" wrote
in
message
...
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.