General Excel question

[dolpandotcom]

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

 

[Eduardo]

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

 

[Sheeloo]

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:B20>50))

 

[Shane Devenshire]

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

 

[dolpandotcom]

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

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

 

[Eduardo]

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:

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

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

 

[dolpandotcom via OfficeKB.com]

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

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

 

[dolpandotcom via OfficeKB.com]

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

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]

 

[Gord Dibben]

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,

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

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

 

[dolpandotcom via OfficeKB.com]

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

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]

 

[Gord Dibben]

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

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

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

 

[dolpandotcom via OfficeKB.com]

Hi Gord,

Thanks a million. It is working fine. Since I needed it to work on 6 other
columns, I decided to make the If Target.Column = 3 Then to be IF Target.
Column>=3 which makes it to include other columns that I want to use, but I
will like two or three items selected in a cell to be counted as one using
the COUNT(C8:j8) as an example.

Is there any other statement to add to the COUNTI(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

Thanks.
Dolpan

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

Hi Gord,

[quoted text clipped - 73 lines]

 

[Gord Dibben]

=COUNTA(C8:J8) counts multiple entries in a cell as one.

Also......I would change If Target.Column >= 3 Then to

If Not Application.Intersect(Target, Columns("C:H")) Is Nothing Then

Nothing wrong with your revision except it potentially includes columns past
H


Gord

Hi Gord,

Thanks a million. It is working fine. Since I needed it to work on 6 other
columns, I decided to make the If Target.Column = 3 Then to be IF Target.
Column>=3 which makes it to include other columns that I want to use, but I
will like two or three items selected in a cell to be counted as one using
the COUNT(C8:j8) as an example.

Is there any other statement to add to the COUNTI(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

Thanks.
Dolpan

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

Hi Gord,

[quoted text clipped - 73 lines]

Dolpandotcom
06/16/09

 

[dolpandotcom via OfficeKB.com]

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one
row of data as shown below::

A B
C D
2 2,1
1

If I want to count the numbers that exist on that row as COUNT(Ax:Cx) whrer x
is the row number. The result which will be placed in cell Dx will not be 3
simply because the cell Bx is taken as character probably. My humble question
is that "How do I use the COUNT function to accomplish the task or is ther
any other function that I can use?

Thanks.
Dolpan

=COUNTA(C8:J8) counts multiple entries in a cell as one.

Also......I would change If Target.Column >= 3 Then to

If Not Application.Intersect(Target, Columns("C:H")) Is Nothing Then

Nothing wrong with your revision except it potentially includes columns past
H

Gord

Hi Gord,

[quoted text clipped - 30 lines]

 

[Gord Dibben]

I guess I misunderstood this statement................

Is there any other statement to add to the COUNT(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

You have A1=2, B1=2,1 and C1=1

What do you want as a result in D1?

COUNT will give you 2 because it counts numbers only

COUNTA will give you 3 which seems to be what your starement above asks for.

Do you want to count B1 as two numbers giving you a return of 4 in D1?


Gord

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one
row of data as shown below::

A B
C D
2 2,1
1

If I want to count the numbers that exist on that row as COUNT(Ax:Cx) whrer x
is the row number. The result which will be placed in cell Dx will not be 3
simply because the cell Bx is taken as character probably. My humble question
is that "How do I use the COUNT function to accomplish the task or is ther
any other function that I can use?

Thanks.
Dolpan

=COUNTA(C8:J8) counts multiple entries in a cell as one.

Also......I would change If Target.Column >= 3 Then to

If Not Application.Intersect(Target, Columns("C:H")) Is Nothing Then

Nothing wrong with your revision except it potentially includes columns past
H

Gord

Hi Gord,

[quoted text clipped - 30 lines]

Dolpandotcom
06/16/09

 

[dolpandotcom via OfficeKB.com]

Your COUNTA function is absolutely correct. It has done what I want exactly.

Thanks.
Dolpan

I guess I misunderstood this statement................

Is there any other statement to add to the COUNT(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

You have A1=2, B1=2,1 and C1=1

What do you want as a result in D1?

COUNT will give you 2 because it counts numbers only

COUNTA will give you 3 which seems to be what your starement above asks for.

Do you want to count B1 as two numbers giving you a return of 4 in D1?

Gord

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one

[quoted text clipped - 30 lines]

 

[dolpandotcom via OfficeKB.com]

Hi Gord,

Happy Sunday. I am here again. I tried to use the calendar program in a
worksheet. I did change the cell where I will like to automate the calendar
as so that when one clicks on that cell, the calendar pop up for appropriate
date selection. It gave me an error "Run-time error, Object required. Is it
that it cannot work in excel2003 or I did not change a statement somewhere in
the program?

This is the program which I pasted but only changed the cell A1:A20 to A1
since that is the position where I want the calendar to pop up.

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub


Thanks and enjoy your Sunday.
Dolpan

I guess I misunderstood this statement................

Is there any other statement to add to the COUNT(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

You have A1=2, B1=2,1 and C1=1

What do you want as a result in D1?

COUNT will give you 2 because it counts numbers only

COUNTA will give you 3 which seems to be what your starement above asks for.

Do you want to count B1 as two numbers giving you a return of 4 in D1?

Gord

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one

[quoted text clipped - 30 lines]

 

[dolpandotcom via OfficeKB.com]

I am sending same message again. It was like I did not have a good internet
connection. Find posting below:

Hi Gord,

Happy Sunday. I am here again. I tried to use the calendar program in a
worksheet. I did change the cell where I will like to automate the calendar
as so that when one clicks on that cell, the calendar pop up for appropriate
date selection. It gave me an error "Run-time error, Object required. Is it
that it cannot work in excel2003 or I did not change a statement somewhere in
the program?

This is the program which I pasted but only changed the cell A1:A20 to A1
since that is the position where I want the calendar to pop up.

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub

Thanks and enjoy your Sunday.
Dolpan

I guess I misunderstood this statement................

Is there any other statement to add to the COUNT(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

You have A1=2, B1=2,1 and C1=1

What do you want as a result in D1?

COUNT will give you 2 because it counts numbers only

COUNTA will give you 3 which seems to be what your starement above asks for.

Do you want to count B1 as two numbers giving you a return of 4 in D1?

Gord

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one

[quoted text clipped - 30 lines]

 

[Gord Dibben]

Did you first insert the calendar activex form from the control toolbox>more
controls on your sheet?

Do you have MS Access installed..................you won't have the needed
mscal.ocx file if you haven't.

See Ron de Bruin's site for details and instructions.

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


Gord

Hi Gord,

Happy Sunday. I am here again. I tried to use the calendar program in a
worksheet. I did change the cell where I will like to automate the calendar
as so that when one clicks on that cell, the calendar pop up for appropriate
date selection. It gave me an error "Run-time error, Object required. Is it
that it cannot work in excel2003 or I did not change a statement somewhere in
the program?

This is the program which I pasted but only changed the cell A1:A20 to A1
since that is the position where I want the calendar to pop up.

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub


Thanks and enjoy your Sunday.
Dolpan

I guess I misunderstood this statement................

Is there any other statement to add to the COUNT(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

You have A1=2, B1=2,1 and C1=1

What do you want as a result in D1?

COUNT will give you 2 because it counts numbers only

COUNTA will give you 3 which seems to be what your starement above asks for.

Do you want to count B1 as two numbers giving you a return of 4 in D1?

Gord

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one

[quoted text clipped - 30 lines]

Dolpandotcom
06/16/09

 

[dolpandotcom via OfficeKB.com]

I am using excel2003 and the instuction says that by simply selecting from
the active worksheet excel menu "Object" and further select "Calendar" it
will place the calendar on the sheet. It is like the VB code given only works
for excel 2007. Am I right pls?

Dolpan

Did you first insert the calendar activex form from the control toolbox>more
controls on your sheet?

Do you have MS Access installed..................you won't have the needed
mscal.ocx file if you haven't.

See Ron de Bruin's site for details and instructions.

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

Gord

Hi Gord,

[quoted text clipped - 51 lines]