SIMPLE FORMULA

[andy]

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

 

[Jacob Skaria]

Andy

Try the below..

1. Suppose you have data in Sheet1 with headers in row 1. From Sheet2 access
the menu Data>Filter>Autofilter 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

 

[Francis]

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

 

[Ron Rosenfeld]

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

 

[andy]

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)

 

[Daniel.C]

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(ISNA(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

 

[Jacob Skaria]

You are welcome..

If this post helps click Yes