If/Count IF

D

Delaina

I am using the formula:
=if(countif($h3:$h$4143,h2)=0,"single","")
because I have a list of 4000+ clients and I need to weed out all the
codes that are single. I am getting a "single" on the last client of
the group. For example:
CLIENT |STATUS
ABC |
ABC |
ABC | Single
123 | Single
456 | Single

I am trying to eliminate that last "single" to where it truely is one
singular clients with the word "single." I tried it the opposite way
where I would output "duplicate" but that didn"t seem to work any
better... suggestions are appreciated if you think there is a better
way to find all single clients.
 
B

Bob Phillips

Try

=if(countif($h$3:$h$4143,h2)=0,"single","")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

Danny@Kendal

Delaina said:
I am using the formula:
=if(countif($h3:$h$4143,h2)=0,"single","")
because I have a list of 4000+ clients and I need to weed out all the
codes that are single.
Click to expand...

I'd use

=IF(COUNTIF(Clients,H2)>1,"","single")

and use "Clients" as a named range for $H$3:$H$4143, that way you can adjust
the range or move it to a new location without having to update all the
formulas.
 
D

Delaina

I tried the suggestion and it got rid of all of the output. There was
single and it was the last client on the list.
Any other suggestions?
Thanks
 
D

Danny@Kendal

Danny@Kendal said:
I'd use

=IF(COUNTIF(Clients,H2)>1,"","single")

and use "Clients" as a named range for $H$3:$H$4143, that way you can
adjust the range or move it to a new location without having to update all
the formulas.
Click to expand...

Not quite Mr D! Just realised "H2" wont move when Clients is pointed
somewhere else. Doh!
 
D

Delaina

I found out how to make it work...using both of our formulas

=if(countif($h$3:$h$4243,h2)=1,"single","")

Thanks
 
L

Ltat42a

Expanding on this topic a lil bit. I'm looking to do something very
similar.
I have a list of names(about 20) in column A. In the columns to the
right I have
certain data - some text, some numbers.

What I'm looking to do is count the number of times a person is listed
(it is possible for these names to repeat during the year) in column
A,
then all of the data to the right of that person's name?

For instance, I have -
NAME DATE HOURS TEXT TEXT
John Doe 12/13/05 8 Text Text
Jane Doe 12/12/05 4 Text Text
ETC
ETC


John will be listed several times in column A, I'm wanting to find out
how many times John is listed along with the data in the row.
The results will be on another worksheet.

Is this possible?

Thanx.
 
R

Roger Govier

Hi

On sheet2
=SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$1000="John"))
Change sheet name and range to suit

Regards

Roger Govier
 
L

Ltat42a

Roger said:
Hi

On sheet2
=SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$1000="John"))
Change sheet name and range to suit

Regards

Roger Govier
Click to expand...


Thanx for the reply, I put the above formula on Sheet2, I'm getting th
#VALUE! error. I'm using Excel from Office XP. I've got all my data in
can't figure out why I'm getting the error.

J
 
R

Roger Govier

Hi

My apologies, the formula is missing the number of characters to take, and
the closing bracket after the left statement.

=SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$1000,4)="John"))

Regards

Roger Govier
 
L

Ltat42a

Roger said:
Hi

My apologies, the formula is missing the number of characters to take
and
the closing bracket after the left statement.

=SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$1000,4)="John"))

Regards

Roger Govier
Click to expand...

That worked. It now returns the number of times "John" is listed i
column A,
however, it doesn't copy of the data in columns B-E on the row tha
contains "John".

Is that still possible?

Thanx...J
 
R

Roger Govier

Hi

In that case, i would mark the header row, Data>Filter>Autofilter
Select the dropdown on column A and choose Custom, =LEFT(A2,4)="John"
Copy the block of Filter rows that result.
Paste to another sheet.

Regards

Roger Govier
 
Top