Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Count If Question?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I'm not sure if this is a count if problem but we have gender in one column, M or F and we want to count the Met, Exceed or Did not meet in another column that corresponds to the gender. A formula like if(Gender column=M, count if (Score column, Met)) does not work.
HELP! we need to get this done quickly. We just need to know how many males/females met, exceeded or did not meet.
Male E
Male D
Male M
Female M
Female E
Female D
Male M
Female M

View Answers     

Similar Excel Tutorials

Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
Select Cells in Excel using Macros and VBA
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
Count the Number of Cells that Start or End with Specific Text in Excel
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
Dynamic Formulas that Update When you Add Data in Excel
I'll show you how to make formulas and functions that automatically update when more data is added to a range in E ...

Helpful Excel Macros

Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f

Similar Topics







Hi all newbie here,

I have a list of 44 sports and I need to count how many Males or Females partipate or compete in each sport.


Gender Activity ParticipateCompete
Female None No Yes
Male Angling Yes No
Female None No Yes
Female Basketball No Yes
Male Badminton No Yes
Male Judo Yes No
Female Triathlon No Yes
Female Badminton Yes No
Male Archery Yes No

I have created lists called "activity" for the sports and "gender" and "Yes No" for the compete or participate.

Oh, and I've been asked not to use VBA.

Any ideas?

P


I have a table with 3 columns, column 1 is a list of names. Column 2 is a list of how many people have that name and column 3 is the gender of those people eg. Name Number of people Sex Aaron 2 Male Abaleeza 1 Male Abraham 2 Male Ada 19 Female Adam 3 Male Adolphus 1 Male Agnes 14 Female

I am wanting a formula to count exactly how many people were male and how many were female. I am at a total loss as to what to use, so any help appreciated.


Let's say I have three columns of data.

In Column A, is gender: male/female
In Column B, is name: Bob, Jon, Mary, etc
In Column C, is salary: $40, $30, $20, etc.

In column B, there will be instances where a name might appear more than once. I want to be able to do a sumif formula of Column A and Column C, but not include any records where a name in column B is listed more than once. Is there a way to do this?


1 Male Bob $50
2 Female Jane $30
3 Female Kate $20
4 Male Mike $30
5 Male Bob $50

So if I wanted to sum all male salaries above, it would be $80, not $130.


A
Males B
18-64 C
1 Females 18-64 0.9 Children Males 15-17 0.9 Children Females 15-17 0.8 Children (Male and Female) 6-14 0.6

D
E
F
G
male 54 16 120.5 male 30 32 240.5 male 60 32 240.5 male 60 32 240.5 male 50 4 31 female 34 4 31 female 49 32 240.5 female 43 16 120.5 female 96 8 60.5 female 49 8 60.5 male 71 16 120.5



All I want is this: if E falls within the range in the column B (e.g. 18-64) and D is male, multiply G by C1 (1).

If E falls within the range of 18-64 of column B, and D is female multiply G by C2 (0.9)

If E falls within the range of 15-17 of column B and D is male multiply G by C3 (0.9)

If E falls within the range of 15-17 of column B and D is female multiply G by C4 (0.8)

Finally, if E falls within the range of 6-14 of column B and D is either male or female multiply G by C5 (0.6).

I am definitely stuck and any help would be welcomed.





















































Okay so, I'm trying to sort it in to age and gender categories, I entered the formula and when I clicked enter it didn't say there were any mistakes, but when I actually test if it works, it doesn't. it just comes up as "#VALUE!", This is for my ICT GCSE spreadsheet, oh and here's the formula

=IF(D5<20&AND(D7="Female"),"Female Teenager","Male Teenager"),IF(D5<51&AND(D7="Female"),"Female Adult","Male Adult"),IF(D5<150&AND(D7="Female"),"Female Over 50","Male Over 50"),IF(D5<6&AND(D7="Female"),"Female Under 5","Male Under 5"),IF(D5<11&AND(D7="Female"),"Female Child","Male Child")

ict.jpg

Thanks!

Hi
I'm trying what appears to be a complicated bar chart and I'm struggling to get any decent results... Can anyone help?

I have six variables (V1, V2, V3, V4, V5, V6) which represent groups of people. Each of these groups have a male/female split, and so I am representing each variable's male/female split as a percentage.
i.e.
V1: 52.9% Male; 47.1% Female
V2: 52.7% Male; 47.3% Female
V3: 46.2% Male; 53.8% Female etc...

That has produced a very straightforward bar chart where each bar adds-up to 100%. Simples.

However, I also want to add, below each of the variable's (V1, V2, etc...) bars, another bar that splits each male/female group into three further sub-categories (X1, X2, & X3). So, of the 52.9% who are males within V1, X1=61.84%, X2=3.52%, and X3=34.64%; and of the 47.1% who are females witin V1, X1=19.28%, X2=26.65%, and X3=54.06%.
i.e.
V1: 52.9% Male 47.1% Female
V1': 61.84 X1, 3.52% X2, 34.64 X3; 19.28% X1, 26.65% X2, 54.06% X3

The problem is that Excel doesn't seem to like the fact that the bars representing variables V1-6 are split into two groups (male/female) whereas the bars representing the three further sub-categories are split into six (X1,X2,X3,X1,X2,X3) groups.

Have I made any sense there? These things are always quite tricky to explain...

Any help would be very appreciated!

Thanks




Hi,

Gender Yes/no Male Yes Female No Male Yes Female No


In this table, when the value is Male, corresponding Cell with value "Yes" should be cleared. How can I do it with Macro?

Proposed table should be,

Gender Yes/no Male Female No Male Female No




Dear People,
I am very new to the intricacies of Excel. I want some help.
I created two tables of male and female names using a web utility known as mockaroo. All went fine, no hitches. It makes files of the dot csv extension. I downloaded them and saved them as xls (Excel 2003) files. I even managed to copy the female name excel file at the end of the male name excel file. Then i gave column headings (first_name, last_name, date_of_birth and gender).

Now I have all the female names below the male names. What I would like is to "scramble" the list so the the male and female names are randomly "mixed".

By the way, these are the dummy patient names for a patient information database I am trying to design in Access 2003. In a real world situation patients as they are entered into patient databases will all not be males followed by females or the other way around. So just to mimic an imagined "real life" situation is there a way I can interlace the male and female names so that they are well "mixed", instead of separating into two "layers" of male and female? It would not do if the male and female names are simply alternating.

Thanks in advance!

car313

Hi all,

I'm using the following COUNTA function for a range of cells.

Quote:

=COUNTA(W92:W580)

In column X92:W58 i have a drop down list to pick a gender, (Male , Female). Can I add to the counta function so that I can count all data entered into W92:W580 and get it filtered by gender.

E,g Say there were 10 data entires within the counta range and 3 were female my table would show: Male: 7 Female: 3 Total:10?

Thanks in advance.




I have data with the following structure (simplified version):

Question----ResponseID----Response
Age:-----------0001-------------25
Age:-----------0002-------------30
Age:-----------0003-------------50
Age------------0004-------------20
Gender:-------0001------------Male
Gender:-------0002------------Male
Gender:-------0003-----------Female
Gender:-------0004-----------Female

So that my answers are all in one column.
I need to create a table with 2 criterias (X & Y 'axes' of the table) that are represented by my answers.
For example, to create the following distribution:

-----------------------------Age axis
----------------20----25---30---35---40---45---50
Male----------15%--5%--10%--3%--5%--10%-1%
Female--------20%-15%--5%--8%--2%--0%--1%

Obviously, simple COUNTIFS formula with multiple criteria will not work as there are no intersections of answers on different questions. The only connection between them - is the ResponseID.
I am thinking of something like SUMPRODUCT formula that will include intersection of ages with responseIDs in one array and the intersection of gender with responseIDs in the other array. However, I cannot find out a working formula.
Could someone help please?

I am attempting to create a pie chart with percentages hidden within. For instance,

I have counts of how many people like different subjects, and then want to display how many of them were male and female for each. Ideally, this would all make it into one pie chart via VBA.

Math
15 Total
10 Male
5 Female

Science
11 Total
6 Male
5 Female

Language Arts
9 Total
2 Male
7 Female

Etc..

The primary chart would be the totals, and then imbedded in the same graph, the percentage of male vs female would be displayed.

Any assistance would be a tremendous help! I have been coming up with only dead ends.

Thank you in advance,

Stephen




Hi All,

First time posting here, and am hoping the experts here would be able to lend a hand!

I've included a copy of what the data "sort of" looks like, and what the desired output is.

Pretty much I have Data that sorts people based on gender and position.

The thought would be to "Sort" them into a new table based on their gender/position.

So if it was
Employee A, Male, Waiter
Employee B, Female, Waiter
Employee C, Male, Cook
Employee, D, Female, Cook
Employee E, Female, Waiter

My result set would look like:

Male Waiters: Male Waiters Male Cooks female Waiters female Cooks Employee A Employee B Employee C Employee D Employee E


It seems simple enough but can't seem to get any forumla to work. Would anyone be able to lend a hand? :P

Much appreciated!

Good Afternoon,

I have a row on my sheet F with values that say either Male or Female. How do i change it to just say F if female or M if Male. Sorry to be such a noob.
Im hoping this is really easy.


I am building a weight loss tracker for a contest. I want to rank both the Male team and Female team by total weight lost each week.

What I need to know how to do is in Column U I have a rank formula. What I need it to do is look at columns C and D and determine if the person is male or female and run two different ranks.

For Example if there are 10 people 5 male and 5 female, the rank formula in column U needs to rank just males (column C) 1-5 and then just females (column D) 1-5.

The spreadsheet is attached for reference.


I am trying to do a sort on Windows Based and I want ppl who have had no training. I think I have the criteria set right and I have the data selected and I am copying to a new location. When I have the =0 in the second line it displays one Macintosh with zero's and then the rest is Windows based with the second cirteria not being picked up. So I move the formula on the same line as =Windows-based and then I get all it is just copying the database down. What am I doing wrg?

Thanks
Wendy


First Name Last Name Gender Date Hired Department Computer Type Word Excel PP Access Expense =Windows-based =0 =0 =0 =0 First Name Last Name Gender Date Hired Department Computer Type Word Excel PP Access Expense Chang SUN Male 21-Aug-88 Human Resources Macintosh 0 0 0 0 $0 Jane BOXER Female 5-Jul-89 Accounting Windows-based 1 2 1 3 $350 Sue LORD Female 15-Jan-92 Payoll Windows-based 0 2 0 0 $100 Peter BULLARD Male 12-Jan-95 Marketing Windows-based 1 1 3 0 $250 Wayne WHEATON Male 8-Feb-85 Human Resources Macintosh 1 0 2 0 $150 James BAIRD Male 24-Aug-95 Accounting Windows-based 0 3 1 1 $250 Debby KRASNY Female 12-Dec-95 Accounting Windows-based 1 1 1 2 $250 Ralph STEINBERG Male 13-Jan-86 Branch 2 Macintosh 0 3 1 0 $200 Vickie NOLAN Female 25-Mar-88 Branch 1 Windows-based 2 2 0 1 $250 Wayne ROBERTSON Male 4-Apr-92 Human Resources Macintosh 1 2 0 0 $150 Chantele AUTERMAN Female 6-Oct-92 Branch 3 Windows-based 3 1 0 1 $250 Russ SEMARK Male 7-Aug-84 Marketing Windows-based 2 2 3 1 $400 Robert BAUER Male 9-Nov-84 Purchasing Windows-based 0 0 0 0 $0 William EVICH Male 2-Dec-85 Accounting Windows-based 0 3 0 2 $250 Susan RHODES Female 23-Mar-87 Branch 1 Windows-based 1 1 0 0 $100 Chris HART Female 11-Apr-88 Marketing Windows-based 1 0 3 1 $250 Kay DOBBS Female 25-May-90 Payroll Windows-based 0 3 0 1 $200 Tim HILL Male 9-Sep-92 Branch 2 Macintosh 0 3 2 2 $350 Mike O'SULLIVAN Male 20-May-93 Branch 3 Windows-based 0 3 0 0 $150 Beverly ROBERTS Female 12-Sep-93 Branch 3 Windows-based 1 2 1 2 $300 Pat ROWLAND Female 4-Nov-95 Branch 1 Windows-based 1 2 0 0 $150 Archie BAYLIS Male 1-Jun-97 Branch 3 Windows-based 1 2 0 0 $150 First Name Last Name Gender Date Hired Department Computer Type Word Excel PP Access Expense Chang SUN Male 21-Aug-88 Human Resources Macintosh 0 0 0 0 $0 Jane BOXER Female 5-Jul-89 Accounting Windows-based 1 2 1 3 $350


Hello,

I have an Access database where I have built various forms and queries. However, my weak point is creating reports. I'm hoping to receive some guidance in what I am trying to accomplish.

I have one table where each record lists an applicant's name, sex (male/female), whether or not they are a minority (yes/no), university, and the year applied. I'm trying to create a report that will give the total number of applicants, total number & percentage of applicants who are male/female & minority, and also list each of these by University. So the report would give information something like this:

Total Applicants: 10
Total Female Applicants: 4
Female Percentage: 40%
Total Male Applicants: 6
Male Percentage: 60%

Then a breakdown by University:

Total Applicants from Harvard: 2
Total Female: 1
Female Percentage: 50%
Total Male Applicants: 1
Male Percentage: 50%

Total Applicants from Boston College: 4
Total Female: 3
Female Percentage: 75%
Total Male Applicants: 1
Male Percentage: 25%

Total Applicants from William & Mary: 4
Total Female: 1
Female Percentage: 25%
Total Male Applicants: 3
Male Percentage: 75%

I can create forms, tables & queries just fine, but for some reason reports are giving me a hard time. Any help would be much appreciated.




Hi everyone, I have a column of data which contains names and prefixes (Ms, Mr, Mrs, Miss Doe) and I need to get a new column with either Male or Female depending on the prefix. so Mr become male, Miss becomes female. Any help would be appreciated as Im new to excel.



In a pivot table test I have 20 respondents from two towns in a survey, and I want to count male and female respondents in each town. How set it up? Here are my Catagories:

GENDER TOWN
Male Female Riverhead Southold

I must admit that I do not use excel much, but I MUST finish this analysis tonight and have not been able to get a simple formula to work. Here are my questions. (If I can get one formula to work, I can rewrite it to find others. PLEASE, someone give me the correct formula.
Question

1. Pre test score average by grade (7 & 8)
(column B2:B110 are the pre test scores and column E2:E110 either lists a 7 or an 8 for grade)

2. Pre test score average by gender (Male & Female)
(column G2:G110 list either Male or Female)

3. Pre test score average by gender and by grade
(I think this will require an "countif + countif" command)

Thanks, I know it is supposed to be Christmas, but I need to get this figured out. HELP me PLEASE




I am currently working with the results of a study-- on a deadline. I am calculating how many participants selected each gender.

So first, I determined how many responses were submitted. ONLY rows 2-735 are responses, which means that 734 responses were submitted.

Then I wrote my formulas:

Responded female:
=SUM(LEN(C1:C735)-LEN(SUBSTITUTE(C1:C735,"Female","")))/LEN("Female")

Responded male: (same, but with "Male")

Male to Female Transsexual or Transgender

Female to Male Transsexual or Transgender

Preferred Not to Respond


I also calculated for the same range =countblank(C1:C735) because I wanted to "check my work."

When I calculated the final sum of all responses + blank cells looking for a total of 734, I got 738. I went in and double checked all of my formulas, but they're all good. Ranges are correct. This tells me that I have an error somewhere, because obviously 738 is a bigger number than 734, and I don't know where those 4 extra results are coming from. I also took the liberty to go through ALL of this data and count the blanks, and there were, indeed, 9.

I realize this is wordy, so I've attached the dataset with formulas for reference.

HELP!
(and thank you in advance).



Sheet 1 :
Grade Male Female
Primary
Secondary
High School
College
Gender

Sheet 2 :
Col A : Student Id
Col B: Gender : Here we specify whether the student is male or female
Col C : Here we specify category to which the student falls : Primary, secondary, high school, college
Col D : Age : To which age they are in

Problem :

In Sheet 1,
# I should give the count of students who are male and in
* primary and they are between 4 to 6 years.
* Count of Secondary students and between 6 to 10 years ...

# I should give the count of students who are female and in
* primary and they are between 4 to 6 years.
* Count of Secondary students and between 6 to 10 years

I am creating a list with three columns. Column A - Last Name ---- Column B - First Name ---- Column C - Gender. I want to sort the list by alternating gender. I want the list to go Male, Female, Male, Female.... I can't get the list to sort in this manner. Any suggestions??? I don't know if I need a Macro or......?!?!?!?! I'm desperate!!!

Thanks!


Hi Everybody

In a worksheet I've data in "A1:C500" viz MemId, Gender & JobType. Another worksheet has 2 smaller subsets of of data in "A1:A10" and "B1:B15" Both contains MemId's of say 10 male & 15 female members.

I want to find the conditional total count of all members (male & female combined) whose ID's appear on the second worksheet, the conditions being, e.g. whose JobType = 'Engineer' and/or Gender = 'M'. In other words, of these 25 members whose ID's appear on second worksheet, I want to know how many of these have a JobType = 'Engineer' (It could be either JobType = 'Engineer' or Gender = 'M'. Or, it could be JobType = 'Engineer' & Gender = 'M'

Can anyone suggest any of those magical 'Sumproduct()', 'CountIf()', Index()', Match() combination formulas?

It would be highly appreciated.


Best regards


Deepak Agarwal


Hello all,

I can't find a formula to count the maximum M or F in the same column.

Is the predominent gender in this list, male or female?

GAC

* C 2 Gender 3 F 4 F 5 F 6 F 7 M 8 F 9 M 10 F 11 M 12 M 13 F 14 M 15 F 16 M 17 M

Excel tables to the web >> Excel Jeanie HTML 4


Thanks in Advance,
Paul




Hey all,

I am currently a student who is partaking in compiling my survey results.

I want to pair the variables up in a row so that i am able to see a person's data more properly as shown by the image below below. Ideally this is what i want to achieve.


Question 1 Question 2 Question 3
Age Gender Occupation
51-60 Male Businessmen
21-30 Female Worker
31-40 Male Housewife
21-30 Male Worker
15-20 Male Student
41-50 Female Professional
21-30 Female Professional

However, the arrangement of my current data is not what i want as the results of each person is displayed (as attached)individually and i can't compare them.

SMARTSURVEY.xlsx)

https://www.dropbox.com/s/cmwv7a7qlv...ARTSURVEY.xlsx


I am expecting at least 200 more respondents and i need a way to automatically arrange the data like the above table.

Any help would be greatly appreciated.

Regards
Derek