Counting sort of

[curator]

If I have one coulmn with 10 locations, and one coulmn with the people working at that plants' # in it, I want to see how many of the 10 locations have at least one person reporting out of them is there a formula or something?

I have created an example sheet located here

ftp://lewisystems.net/pub/example.xls

if you could help thanks very much

lewis@lewisystems.net

[curator]

that doesn't seem to work

can you explain in a little more detail?

thanks

-Lewis

[Mark W.]

{=SUM(ISNUMBER(MATCH(IF(C2:C23,B2:B23),B2:B23))+0)}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

[curator]

Mark W you are my hero thank you very much

[Mark W.]

On 2002-05-16 17:18, curator wrote:
Mark W you are my hero thank you very much [img]/board/images/smiles/icon_biggrin.gif[/img]

Hmmm, I found a flaw!!!... Please allow me to go back to the drawing board.

[curator]

yeah mark, it doesn't work correctly...

[Yogi Anand]

Curator: If you can sort your data by column B in ascending order, and by column C in descending order ... then you can use the following DCOUNTA function

=DCOUNTA($B$1:$C$23,1,E9:E10)
with the following entries in cells E9, E10 and E11
E9 ... blank
E10 ... =AND(B2<>B1,C2>=1)

Regards!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 16:57 ]

[Mark W.]

Well, this array formula works...

{=COUNT(IF(FREQUENCY(IF(C2:C23,COUNTIF(B2:B23,B2:B23)),ROW(B1:B23)),1))}


Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

[ This Message was edited by: Mark W. on 2002-05-16 17:49 ]

[Yogi Anand]

You can also use Advanced Filter

Criteria being # OF RECORDS >0
UNIQUE records only

and that will give you 2 records, your record nos 1 and 5

Regards!

[Aladin Akyurek]

Since you just want determine how many non-zero values you have in column C, I would think

=COUNT(C2:C23)-COUNTIF(C2:C23,0)

would suffice.

For the sample:

{"TECH #","LOCATION","# OF RECORDS";
1,"A ",12;
2,"B",0;
3,"A",0;
4,"A",0;
5,"B",2;
6,"B",0;
7,"A",0;
8,"A",0;
9,"B",0;
10,"A",0;
11,"C",0;
12,"C",0;
13,"E",0;
14,"D",0;
15,"F",0;
16,"F",0;
17,"F",0;
18,"F",0;
19,"C",0;
20,"D",0;
21,"E",0;
22,"B",0}

the result is 2.

Aladin