Count students
Hi Oscar,
I have the following issues. Any suggestions.
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 are 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
Kindly let me know of any specific formula e that I can use.
Answer
Sheet 2  Example data
Sheet 1  Count students
Formula in cell B2:
You can copy this formula to cell range B2:C5. The formula takes advantage of both absolute and relative cell references . For this to work, copy the cell, not the formula in the cell or formula bar.
Example,
 Select cell B2
 Copy cell B2 (Ctrl + c)
 Select cell range B2:C5
 Paste (Ctrl + v)
Read more about absolute and relative cell references
Formula in cell B6:
If you are familiar with array formulas, use this array formula in cell B6:
The formula is smaller but needs to be entered as an array formula.
How to enter an array formula
 Select cell B6

Paste formula to formula bar
 Press and hold CTRL + SHIFT
 Press Enter simultaneously
 Release all keys
If you did it right, the formula now begins and ends with curly brackets, like this:
Explaining formula in cell B2
Step 1  Compare value in cell A2 (sheet1) to values in cell range C2:C169 (sheet2)
$A2=Sheet2!$C$2:$C$169
becomes
"Primary"={"College"; "College"; "High school"; ... ; "Secondary"}
and returns
{FALSE; FALSE; FALSE; ... ; FALSE}
Step 2  Compare value in cell B1 (sheet1) to values in cell range B2:B169 (sheet2)
B$1=Sheet2!$B$2:$B$169
becomes
"Male"={"Male"; "Female"; "Male"; ... ; "Female"}
and returns
{"TRUE"; "FALSE"; "TRUE"; ... ; "FALSE"}
Step 3  Multiplying arrays
($A2=Sheet2!$C$2:$C$169)*(B$1=Sheet2!$B$2:$B$169)
becomes
("Primary"={"College"; "College"; "High school"; ... ; "Secondary"})*("Male"={"Male"; "Female"; "Male"; ... ; "Female"})
becomes
({FALSE; FALSE; FALSE; ... ; FALSE})*({"TRUE"; "FALSE"; "TRUE"; ... ; "FALSE"})
and returns
({0; 0; 0; ... ; 0})
Step 4  Sum all values in the array
=SUMPRODUCT(($A2=Sheet2!$C$2:$C$169)*(B$1=Sheet2!$B$2:$B$169))
becomes
=SUMPRODUCT({0; 0; 0; ... ; 0})
and returns 5 in cell B2.
Explaining formula in cell B6
Step 1  Compare value in cell B1 (sheet1) to values in cell range B2:B169 (sheet2)
B$1=Sheet2!$B$2:$B$169
becomes
"Male"={"Male"; "Female"; "Male"; .. ; "Female"}
and returns
{"TRUE"; "FALSE"; "TRUE"; .. ; "FALSE"}
Step 2  Convert boolean values to numerical values
SUMPRODUCT function (and the sum function) canยดt sum boolean values (TRUE, FALSE). Multiplying with 1 converts TRUE to 1 and FALSE to 0.
(B$1=Sheet2!$B$2:$B$169)*1
becomes
{"TRUE"; "FALSE"; "TRUE"; .. ; "FALSE"}*1
and returns
{1; 0; 1; .. ; 0}
Step 3  Sum values
SUMPRODUCT((B$1=Sheet2!$B$2:$B$169)*1)
becomes
SUMPRODUCT({1; 0; 1; .. ; 0})
and returns 71 in cell B6.
More to note
You can also use the SUMIF and SUMIFS function to count values. See this post:
Sum values between two dates with criteria
Download excel *.xlsx file
Functions in this post
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
SUM(number1,[number2],)
Adds all the numbers in a range of cells
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this [โฆ]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are [โฆ]
Count cells between specified values
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values [โฆ]
Count unique distinct values in two columns
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double click on cell C12 [โฆ]
Count unique distinct values in a filtered Excel defined Table
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in [โฆ]
Count unique distinct values within same week, month or year
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 [โฆ]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges [โฆ]
How to count unique distinct values based on a date
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. [โฆ]
Count cells between two values
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 [โฆ]
Count overlapping days in multiple date ranges, part 2
In the previous post I explained how to count overlapping dates between a single date range and multiple date ranges. In [โฆ]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form