I have two columns in an excel sheet. One column shows 1 representing male
and 2 represnting female. In the second column I have marks scored by males
and females in a class. In all, I have 20 rows used to store the data for
males and females. The storage is not in any particular order.

Can you please let me know how i can either use the if condition or the
ifcount function to count the number males and females that scored a specific
mark, say 50% in the class?

Thanks.
Dolpandotcom
06/16/09

Hi,
If you want to just count the total of Male and Female that achieved 50%

=sumproduct(--($B$1:$B$20=0.5))

if you want to count who achieve 50% or more

=sumproduct(--($B$1:$B$20=0.5))

if this helps please click yes, thands

"dolpandotcom" wrote:

I have two columns in an excel sheet. One column shows 1 representing male
and 2 represnting female. In the second column I have marks scored by males
and females in a class. In all, I have 20 rows used to store the data for
males and females. The storage is not in any particular order.

Can you please let me know how i can either use the if condition or the
ifcount function to count the number males and females that scored a specific
mark, say 50% in the class?

Thanks.
Dolpandotcom
06/16/09

Thanks, but this exactly what I have in an Excel sheet:

Sex score
1 50
1 30
2 50
2 25
2 50
1 50

From the above table, I will like to know how can use an IF or IFcount to
generate a result such as saying three male had the score of 50, while two
females also had 50.

Please note that 1 refers to Male while 2 refers to female.

I will appreciate your respond Edardo or any other person pls.

Thanks.
Dolpan

Eduardo wrote:
Hi,
If you want to just count the total of Male and Female that achieved 50%

=sumproduct(--($B$1:$B$20=0.5))

if you want to count who achieve 50% or more

=sumproduct(--($B$1:$B$20=0.5))

if this helps please click yes, thands

I have two columns in an excel sheet. One column shows 1 representing male
and 2 represnting female. In the second column I have marks scored by males
[quoted text clipped - 8 lines]
Dolpandotcom
06/16/09

Hi
Sex in column A and score in column B

To get the results for 1

=SUMPRODUCT(--(A1:A14=1),--(B1:B14=50))

To get the results for 2

=SUMPRODUCT(--(A1:A14=2),--(B1:B14=50))

"dolpandotcom" wrote:

Thanks, but this exactly what I have in an Excel sheet:

Sex score
1 50
1 30
2 50
2 25
2 50
1 50

From the above table, I will like to know how can use an IF or IFcount to
generate a result such as saying three male had the score of 50, while two
females also had 50.

Please note that 1 refers to Male while 2 refers to female.

I will appreciate your respond Edardo or any other person pls.

Thanks.
Dolpan

Eduardo wrote:
Hi,
If you want to just count the total of Male and Female that achieved 50%

=sumproduct(--($B$1:$B$20=0.5))

if you want to count who achieve 50% or more

=sumproduct(--($B$1:$B$20=0.5))

if this helps please click yes, thands

I have two columns in an excel sheet. One column shows 1 representing male
and 2 represnting female. In the second column I have marks scored by males
[quoted text clipped - 8 lines]
Dolpandotcom
06/16/09

Hi Eduardo,

Thanks a lot. Your script did the magic for me. I still have two more
questions for you and others please,

1. How can I select two items on my dropdown menu created using the data
validation method in Excel 2003? Presently I can only select one item, but I
will like to be able to select two items at a time.

2. How can I make calendar to pupup so as to be able to select month/date
please?

3. Is it possible to write a script to reject a name that already features
among my client list in an excel sheet: Please give me some clues or the
script for it.

Cheers,
Dolpan


Eduardo wrote:
Hi
Sex in column A and score in column B

To get the results for 1

=SUMPRODUCT(--(A1:A14=1),--(B1:B14=50))

To get the results for 2

=SUMPRODUCT(--(A1:A14=2),--(B1:B14=50))

Thanks, but this exactly what I have in an Excel sheet:

[quoted text clipped - 33 lines]
Dolpandotcom
06/16/09

--
Message posted via http://www.officekb.com

If you want to count males and females with 50 marks then use
=COUNTIF(B1:B20,"50")
Change 50 to appropriate %.

If you want males only use
=SUMPRODUCT(--(A1:A20=1),--(B1:B2050))
"dolpandotcom" wrote:

I have two columns in an excel sheet. One column shows 1 representing male
and 2 represnting female. In the second column I have marks scored by males
and females in a class. In all, I have 20 rows used to store the data for
males and females. The storage is not in any particular order.

Can you please let me know how i can either use the if condition or the
ifcount function to count the number males and females that scored a specific
mark, say 50% in the class?

Thanks.
Dolpandotcom
06/16/09

Hi,

1. Enter the value (mark) you want in D1
2. Use the formula
=COUNTIF(B1:B20,D1)

If you want just males or just females with a particular score, in 2007 you
could use
=COUNTIFS(B1:B20,D1,A1:A20,E1)
where E1 contains the number 1 or 2 to indicate male or female and D1 is as
above.

If you want to count both males and females with scores between 40% and 50%
for example
2007:
=COUNTIFS(B1:B20,D1,B1:B20,E1)
with =.4 in D1 and <=.5 in E1
2003
=SUMPRODUCT((B1:B20=D1)*(B1:B20<=E1))
with .4 in D1 and .5 in E1

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"dolpandotcom" wrote:

I have two columns in an excel sheet. One column shows 1 representing male
and 2 represnting female. In the second column I have marks scored by males
and females in a class. In all, I have 20 rows used to store the data for
males and females. The storage is not in any particular order.

Can you please let me know how i can either use the if condition or the
ifcount function to count the number males and females that scored a specific
mark, say 50% in the class?

Thanks.
Dolpandotcom
06/16/09

Hi Shane,

Thanks a lot. Your script for the Excel 2003 did the magic for me. I still
have two more questions for you and others please,

1. How can I select two items on my dropdown menu created using the data
validation method in Excel 2003? Presently I can only select one item, but I
will like to be able to select two items at a time.

2. How can I make calendar to pupup so as to be able to select month/date
please?

3. Is it possible to write a script to reject a name that already features
among my client list in an excel sheet: Please give me some clues or the
script for it.

Cheers,
Dolpan


Shane Devenshire wrote:
Hi,

1. Enter the value (mark) you want in D1
2. Use the formula
=COUNTIF(B1:B20,D1)

If you want just males or just females with a particular score, in 2007 you
could use
=COUNTIFS(B1:B20,D1,A1:A20,E1)
where E1 contains the number 1 or 2 to indicate male or female and D1 is as
above.

If you want to count both males and females with scores between 40% and 50%
for example
2007:
=COUNTIFS(B1:B20,D1,B1:B20,E1)
with =.4 in D1 and <=.5 in E1
2003
=SUMPRODUCT((B1:B20=D1)*(B1:B20<=E1))
with .4 in D1 and .5 in E1

I have two columns in an excel sheet. One column shows 1 representing male
and 2 represnting female. In the second column I have marks scored by males
[quoted text clipped - 8 lines]
Dolpandotcom
06/16/09

--
Message posted via http://www.officekb.com

Question 1.

Download a sample workbook from Debra Dalgleish's site.

http://www.contextures.on.ca/excelfiles.html#DV0017

Question 2.

See Ron de Bruin's site

http://www.rondebruin.nl/calendar.htm

Question 3.

See Chip Pearson's site.

http://www.cpearson.com/excel/NoDupEntry.aspx


Gord Dibben MS Excel MVP

On Wed, 17 Jun 2009 07:38:07 GMT, "dolpandotcom via OfficeKB.com"
<u52610@uwe wrote:

Hi Shane,

Thanks a lot. Your script for the Excel 2003 did the magic for me. I still
have two more questions for you and others please,

1. How can I select two items on my dropdown menu created using the data
validation method in Excel 2003? Presently I can only select one item, but I
will like to be able to select two items at a time.

2. How can I make calendar to pupup so as to be able to select month/date
please?

3. Is it possible to write a script to reject a name that already features
among my client list in an excel sheet: Please give me some clues or the
script for it.

Cheers,
Dolpan


Shane Devenshire wrote:
Hi,

1. Enter the value (mark) you want in D1
2. Use the formula
=COUNTIF(B1:B20,D1)

If you want just males or just females with a particular score, in 2007 you
could use
=COUNTIFS(B1:B20,D1,A1:A20,E1)
where E1 contains the number 1 or 2 to indicate male or female and D1 is as
above.

If you want to count both males and females with scores between 40% and 50%
for example
2007:
=COUNTIFS(B1:B20,D1,B1:B20,E1)
with =.4 in D1 and <=.5 in E1
2003
=SUMPRODUCT((B1:B20=D1)*(B1:B20<=E1))
with .4 in D1 and .5 in E1

I have two columns in an excel sheet. One column shows 1 representing male
and 2 represnting female. In the second column I have marks scored by males
[quoted text clipped - 8 lines]
Dolpandotcom
06/16/09

Hi Gord,

Thanks for the link. I did download of the first link which enables multiple
data item selection into one cell. I copied the code into the VB editor in my
own worksheet. I lunched the MACRO in my vb editor layout, but i did not work
as it was with the downloaded sheet.

Pls see below the codes if I need to change any instruction :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub


Any other person can equally assist in this direction.
Thanks.
Dolpan
Gord Dibben wrote:
Question 1.

Download a sample workbook from Debra Dalgleish's site.

http://www.contextures.on.ca/excelfiles.html#DV0017

Question 2.

See Ron de Bruin's site

http://www.rondebruin.nl/calendar.htm

Question 3.

See Chip Pearson's site.

http://www.cpearson.com/excel/NoDupEntry.aspx

Gord Dibben MS Excel MVP

Hi Shane,

[quoted text clipped - 41 lines]
Dolpandotcom
06/16/09

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200906/1

The code you posted is from the "SameCell" sheet in Debra's sample workbook.

Are you sure you pasted it into the appropriate sheet module in your
workbook?

This code is event code and is "lunched"(sic) only when a selection is made
from a dropdown list in column C

If your dropdown is not in column C you must edit this line to your column
number

If Target.Column = 3 Then


Gord

On Thu, 18 Jun 2009 12:56:21 GMT, "dolpandotcom via OfficeKB.com"
<u52610@uwe wrote:

Hi Gord,

Thanks for the link. I did download of the first link which enables multiple
data item selection into one cell. I copied the code into the VB editor in my
own worksheet. I lunched the MACRO in my vb editor layout, but i did not work
as it was with the downloaded sheet.

Pls see below the codes if I need to change any instruction :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub


Any other person can equally assist in this direction.
Thanks.
Dolpan
Gord Dibben wrote:
Question 1.

Download a sample workbook from Debra Dalgleish's site.

http://www.contextures.on.ca/excelfiles.html#DV0017

Question 2.

See Ron de Bruin's site

http://www.rondebruin.nl/calendar.htm

Question 3.

See Chip Pearson's site.

http://www.cpearson.com/excel/NoDupEntry.aspx

Gord Dibben MS Excel MVP

Hi Shane,

[quoted text clipped - 41 lines]
Dolpandotcom
06/16/09