can anyone please help me???

[LMF]

I have tried methods that i know, but it doesn't work, so i hope there is an expert with this who can help me out with this bit!
ok, i got two column with these data:

Round Pos
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
1 Incompleted
1 Incompleted
1 Incompleted
2 1
2 2
2 3
2 4
2 5
2 6
2 7
2 8
2 Incompleted
2 Incompleted
2 Incompleted

I want the number of ppl with a Position (mean every1 except those who incompleted it). for round both round 1 and 2.

So the experted result is :

Round 1 10 ppl finished it
Round 2 8 ppl finished it

but of course i need to do this excel, can anyone pls teach me how i can do this please?

please help

[LMF]

By the way, please note that the data in such list may not be in order, that mean some result of round 1 could be at the bottom of the result list and some of them could well be at the very top... and please note that i cannot sort this list.

is there anyway around this still?

pls help

[Aladin Akyurek]

On 2002-04-13 14:01, LMF wrote:
I have tried methods that i know, but it doesn't work, so i hope there is an expert with this who can help me out with this bit!
ok, i got two column with these data:

Round Pos
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
1 Incompleted
1 Incompleted
1 Incompleted
2 1
2 2
2 3
2 4
2 5
2 6
2 7
2 8
2 Incompleted
2 Incompleted
2 Incompleted

I want the number of ppl with a Position (mean every1 except those who incompleted it). for round both round 1 and 2.

So the experted result is :

Round 1 10 ppl finished it
Round 2 8 ppl finished it

but of course i need to do this excel, can anyone pls teach me how i can do this please?

please help

[img]/board/images/smiles/icon_frown.gif[/img]

=COUNTIF(A2:A50,1)-COUNTIF(B2:B50,"Incompleted)

=COUNTIF(A2:A50,2)-COUNTIF(B2:B50,"Incompleted)

where A2:A50 houses the round data and B2:B50 the pos data. Adjust to suit,

[LMF]

thanks for helping, but
it doesn't seem to work because:

=COUNTIF(A2:A50,1) will be 13

-COUNTIF(B2:B50,"Incompleted) will be 6

but actually there are only 3 incompleted in round 1, so this won't work...

but pls think of any other solution about this please?

[Ricky Morris]

assuming that the data you're working with is in a range named "Data" try the following:

in cell F1 enter "Round"
in cell F3 enter "1"
in cell G1 enter "Round"
in cell G3 enter "2"
# of people finished in round 1 is:
=DMAX(Data,"Pos",F1:F2)

# of people finished in round 2 is:
=DMAX(Data,"Pos",G1:G2)

[Ricky Morris]

Oops!

I meant in F2 enter "1" and in G2 enter "2"

[Aladin Akyurek]

On 2002-04-13 14:09, LMF wrote:
thanks for helping, but
it doesn't seem to work because:

=COUNTIF(A2:A50,1) will be 13

-COUNTIF(B2:B50,"Incompleted) will be 6

but actually there are only 3 incompleted in round 1, so this won't work...

but pls think of any other solution about this please?

You're right. How disturbing!

=COUNTIF(A2:A50,1)-SUMPRODUCT((A2:A50=1)*(B2:B50="Incompleted"))

The same logic for Round value of 2.

Improved version: Since Yogi Anand provided a DCOUNT version, I'll take up just the SUMPRODUCT version, which should be shortened to:

SUMPRODUCT((A2:A50=1)*(B2:B50<>"Incompleted"))

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-04-13 15:53 ]

[LMF]

yo, Ricky, the Dmax thing doesn't seem to work when the list is not in order...

as i said:

please note that the data in such list may not be in order, that mean some result of round 1 could be at the bottom of the result list and some of them could well be at the very top... and please note that i cannot sort this list.

[LMF]

Aladin, your method makes it works.

thank you very much for your help!



thanks to Ricky too

[Yogi Anand]

Hi LMF:
Try the formula:

=COUNTIF(B1:B25,">=1")
this will give you 18 for those who have completed both rounds 1 and 2
HTH