Dear all, i hope somene can help - because ive just pulled out my last hair
i'll tryto explain - in column A i have a list of 30 names (which may be
duplicated in some places) - in column B i have two possible entries against
each name "OK" or "DI"

What i would like to do is have a formula that would count the name and the
number of "OK" and "DI" together
I could then on a seperate sheet have the one name and the total of OK and
DI against the name

simple for some of u - but not for me - sorry

Regards

Andy the novice

Andy

Try the below..

1. Suppose you have data in Sheet1 with headers in row 1. From Sheet2 access
the menu DataFilterAutofilter and 'Copy to another location' .
In list range type Sheet1!A1:A31
In copy to type/select cell A1

2. In Sheet2 Type the headers in Row1.
B1 = OK
C1 = DI

3. In cell B2 of Sheet2 apply below formula and copy that to right column C2
and down rows as required

=SUMPRODUCT(--(Sheet1!$A$2:$A$31=$A2),--(Sheet1!$B$2:$B$31=B$1))


If this post helps click Yes
---------------
Jacob Skaria


"andy" wrote:

Dear all, i hope somene can help - because ive just pulled out my last hair
i'll tryto explain - in column A i have a list of 30 names (which may be
duplicated in some places) - in column B i have two possible entries against
each name "OK" or "DI"

What i would like to do is have a formula that would count the name and the
number of "OK" and "DI" together
I could then on a seperate sheet have the one name and the total of OK and
DI against the name

simple for some of u - but not for me - sorry

Regards

Andy the novice

Do I understand you correctly, you want to count the total number of OK and DI
for a given name? if yes

In Sheet2 ,
in A2, type the name
in B2, try the below formula
for the example below
=SUMPRODUCT(--(Sheet1!A2:A11=A2),--(Sheet1!B2:B11="OK")+(Sheet1!B2:B11="DI"))
this will give you 2 for the name Mike
name type
dave OK
mike OK
joey ok
ken DI
dave DI
ken OK
mike DI
ken DI
joey OK
joey OK
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"andy" wrote:

Dear all, i hope somene can help - because ive just pulled out my last hair
i'll tryto explain - in column A i have a list of 30 names (which may be
duplicated in some places) - in column B i have two possible entries against
each name "OK" or "DI"

What i would like to do is have a formula that would count the name and the
number of "OK" and "DI" together
I could then on a seperate sheet have the one name and the total of OK and
DI against the name

simple for some of u - but not for me - sorry

Regards

Andy the novice

On Sun, 21 Jun 2009 00:50:01 -0700, andy
wrote:

Dear all, i hope somene can help - because ive just pulled out my last hair
i'll tryto explain - in column A i have a list of 30 names (which may be
duplicated in some places) - in column B i have two possible entries against
each name "OK" or "DI"

What i would like to do is have a formula that would count the name and the
number of "OK" and "DI" together
I could then on a seperate sheet have the one name and the total of OK and
DI against the name

simple for some of u - but not for me - sorry

Regards

Andy the novice


Perhaps a Pivot Table?

Select a cell in your data table.

The Insert/Pivot Table

Drag Names to the row area
Drag Type to the Data or Value area
Drag Type also to the Columns area

A table like:

name type
dave OK
mike OK
joey ok
ken DI
dave DI
ken OK
mike DI
ken DI
joey OK
joey OK


Winds up showing a report like:

Count of type
Names DI OK Total
dave 1 1 2
joey 3 3
ken 2 1 3
mike 1 1 2
Total 4 6 10

The report can be formatted in various ways.
--ron

Francis & Jacob both works a treat for your answers, Ron - i dont really
understand pivots ( another failure for me) nor how to get graphs working
with them so i am going to use Francis method - it gives me 90% of what i
need but i will need to work out how to get the remaining - (not your
answers my requirments).

Thanks guys -

I may be back

regards

Andy francis (yes francis is family name)



"Francis" wrote:

Do I understand you correctly, you want to count the total number of OK and DI
for a given name? if yes

In Sheet2 ,
in A2, type the name
in B2, try the below formula
for the example below
=SUMPRODUCT(--(Sheet1!A2:A11=A2),--(Sheet1!B2:B11="OK")+(Sheet1!B2:B11="DI"))
this will give you 2 for the name Mike
name type
dave OK
mike OK
joey ok
ken DI
dave DI
ken OK
mike DI
ken DI
joey OK
joey OK
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"andy" wrote:

Dear all, i hope somene can help - because ive just pulled out my last hair
i'll tryto explain - in column A i have a list of 30 names (which may be
duplicated in some places) - in column B i have two possible entries against
each name "OK" or "DI"

What i would like to do is have a formula that would count the name and the
number of "OK" and "DI" together
I could then on a seperate sheet have the one name and the total of OK and
DI against the name

simple for some of u - but not for me - sorry

Regards

Andy the novice

On the same sheet, in F1 :
=A1
in F2 :
=IF(ISERR(SMALL(IF(ISNA(MATCH($A$1:$A$30,$F$1:F1,0 )),ROW($1:$30)),1)),"",INDEX($A$1:$A$30,SMALL(IF(I SNA(MATCH($A$1:$A$30,$F$1:F1,0)),ROW($1:$30)),1)))
(Array formula, validate with Ctrl+Shift+Enter)
in G1 :
=SUMPRODUCT(($A$1:$A$30=F1)*($B$1:$B$30="OK"))
in H1 :
=SUMPRODUCT(($A$1:$A$30=F1)*($B$1:$B$30="DI"))
HTH
Daniel

Dear all, i hope somene can help - because ive just pulled out my last hair
i'll tryto explain - in column A i have a list of 30 names (which may be
duplicated in some places) - in column B i have two possible entries against
each name "OK" or "DI"

What i would like to do is have a formula that would count the name and the
number of "OK" and "DI" together
I could then on a seperate sheet have the one name and the total of OK and
DI against the name

simple for some of u - but not for me - sorry

Regards

Andy the novice

On the same sheet, in F1 :
=A1
in F2:Fn (drag down as far as needed) :
=IF(ISERR(SMALL(IF(ISNA(MATCH($A$1:$A$30,$F$1:F1,0 )),ROW($1:$30)),1)),"",INDEX($A$1:$A$30,SMALL(IF(I SNA(MATCH($A$1:$A$30,$F$1:F1,0)),ROW($1:$30)),1)))
(Array formula, validate with Ctrl+Shift+Enter)
in G1 :
=SUMPRODUCT(($A$1:$A$30=F1)*($B$1:$B$30="OK"))
in H1 :
=SUMPRODUCT(($A$1:$A$30=F1)*($B$1:$B$30="DI"))
drag down as far as needed.
HTH
Daniel


On the same sheet, in F1 :
=A1
in F2 :
=IF(ISERR(SMALL(IF(ISNA(MATCH($A$1:$A$30,$F$1:F1,0 )),ROW($1:$30)),1)),"",INDEX($A$1:$A$30,SMALL(IF(I SNA(MATCH($A$1:$A$30,$F$1:F1,0)),ROW($1:$30)),1)))
(Array formula, validate with Ctrl+Shift+Enter)
in G1 :
=SUMPRODUCT(($A$1:$A$30=F1)*($B$1:$B$30="OK"))
in H1 :
=SUMPRODUCT(($A$1:$A$30=F1)*($B$1:$B$30="DI"))
HTH
Daniel

You are welcome..

If this post helps click Yes
---------------
Jacob Skaria


"andy" wrote:

Francis & Jacob both works a treat for your answers, Ron - i dont really
understand pivots ( another failure for me) nor how to get graphs working
with them so i am going to use Francis method - it gives me 90% of what i
need but i will need to work out how to get the remaining - (not your
answers my requirments).

Thanks guys -

I may be back

regards

Andy francis (yes francis is family name)



"Francis" wrote:

Do I understand you correctly, you want to count the total number of OK and DI
for a given name? if yes

In Sheet2 ,
in A2, type the name
in B2, try the below formula
for the example below
=SUMPRODUCT(--(Sheet1!A2:A11=A2),--(Sheet1!B2:B11="OK")+(Sheet1!B2:B11="DI"))
this will give you 2 for the name Mike
name type
dave OK
mike OK
joey ok
ken DI
dave DI
ken OK
mike DI
ken DI
joey OK
joey OK
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"andy" wrote:

Dear all, i hope somene can help - because ive just pulled out my last hair
i'll tryto explain - in column A i have a list of 30 names (which may be
duplicated in some places) - in column B i have two possible entries against
each name "OK" or "DI"

What i would like to do is have a formula that would count the name and the
number of "OK" and "DI" together
I could then on a seperate sheet have the one name and the total of OK and
DI against the name

simple for some of u - but not for me - sorry

Regards

Andy the novice