I have two columns, one with numbers and another with yes or no. In the
number column I will have 5 digit numbers that may repeat 2 to three times in
that column and along side in the other column it will say either yes or no.
For any particular number it will only say yes or no, it wont be yes in one
row and no in another. This information gets uplinked from another sheet and
hence is that way. heres a sample

Warranty W.O.
YES 66888
YES 66888
NO 67471
NO 67471
YES 66992
YES 66992
YES 66992
YES 66948
YES 66948
NO 66945

Now what I need to do is count the number of W.O's that were waranty and
which were not, without couting twice or thrice for the same W.O. So in this
example it would need to be a small table like this

Warranty Yes NO
Number of WO's 3 2

would really appreciate any help. Thanks

Just wondering if I can adapt the formula for counting unique entries
=SUM(N(FREQUENCY(A1:A10,A1:A10)0))
to be able to count the unique entries only if there is a yes in the column
to its left.


"areezm" wrote:

I have two columns, one with numbers and another with yes or no. In the
number column I will have 5 digit numbers that may repeat 2 to three times in
that column and along side in the other column it will say either yes or no.
For any particular number it will only say yes or no, it wont be yes in one
row and no in another. This information gets uplinked from another sheet and
hence is that way. heres a sample

Warranty W.O.
YES 66888
YES 66888
NO 67471
NO 67471
YES 66992
YES 66992
YES 66992
YES 66948
YES 66948
NO 66945

Now what I need to do is count the number of W.O's that were waranty and
which were not, without couting twice or thrice for the same W.O. So in this
example it would need to be a small table like this

Warranty Yes NO
Number of WO's 3 2

would really appreciate any help. Thanks

Try Data -- Filter -- Advanced Filter...
Select Copy to another location
List range is your list of two columns
Criteria range leave blank
Copy to cell D1 or something
Check Unique records only

Then you can do:
=COUNTIF(D:D,"YES") and =COUNTIF(D:D,"NO") to count your two values

-Simon

"areezm" wrote:

Just wondering if I can adapt the formula for counting unique entries
=SUM(N(FREQUENCY(A1:A10,A1:A10)0))
to be able to count the unique entries only if there is a yes in the column
to its left.


"areezm" wrote:

I have two columns, one with numbers and another with yes or no. In the
number column I will have 5 digit numbers that may repeat 2 to three times in
that column and along side in the other column it will say either yes or no.
For any particular number it will only say yes or no, it wont be yes in one
row and no in another. This information gets uplinked from another sheet and
hence is that way. heres a sample

Warranty W.O.
YES 66888
YES 66888
NO 67471
NO 67471
YES 66992
YES 66992
YES 66992
YES 66948
YES 66948
NO 66945

Now what I need to do is count the number of W.O's that were waranty and
which were not, without couting twice or thrice for the same W.O. So in this
example it would need to be a small table like this

Warranty Yes NO
Number of WO's 3 2

would really appreciate any help. Thanks

Using data filter will not work automatically the next time I need to use
this template. This is a template to be used by various people who may not
know how to data sort.

for now I have set up another column where it will copy the work order
number to this column if it says yes in the left column. I used this command,
=IF(A2="YES",B2,)

Then from this column I used the command to identify number of unique data
entries. This is the command,
=(SUM(N(FREQUENCY(D2:D100,D2:D100)0)))-1

This is working for most of my sheets, but the problem is that I have one
sheet in which there are over 50 categories instead of the two YES or NO.
This method will be too confusing and time consuming so is there any command
or formula I can use instead.???

Thanks though Simon

"SimonCC" wrote:

Try Data -- Filter -- Advanced Filter...
Select Copy to another location
List range is your list of two columns
Criteria range leave blank
Copy to cell D1 or something
Check Unique records only

Then you can do:
=COUNTIF(D:D,"YES") and =COUNTIF(D:D,"NO") to count your two values

-Simon

"areezm" wrote:

Just wondering if I can adapt the formula for counting unique entries
=SUM(N(FREQUENCY(A1:A10,A1:A10)0))
to be able to count the unique entries only if there is a yes in the column
to its left.


"areezm" wrote:

I have two columns, one with numbers and another with yes or no. In the
number column I will have 5 digit numbers that may repeat 2 to three times in
that column and along side in the other column it will say either yes or no.
For any particular number it will only say yes or no, it wont be yes in one
row and no in another. This information gets uplinked from another sheet and
hence is that way. heres a sample

Warranty W.O.
YES 66888
YES 66888
NO 67471
NO 67471
YES 66992
YES 66992
YES 66992
YES 66948
YES 66948
NO 66945

Now what I need to do is count the number of W.O's that were waranty and
which were not, without couting twice or thrice for the same W.O. So in this
example it would need to be a small table like this

Warranty Yes NO
Number of WO's 3 2

would really appreciate any help. Thanks