## 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

### Category: Count values

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. Tip! I highly […]Comments(93) Filed in category: Count unique distinct values, Count values, Excel

Count unique distinct values in a column

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: First, let me explain […]Comments(26) Filed in category: Count unique distinct values, Count values, Excel

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]Comments(26) Filed in category: Conditional formatting, Count values, Excel

Count number of times a string exist in multiple cells using excel formula

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]Comments(19) Filed in category: Count text values, Count values, Excel

Excel: List intervals between two values

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]Comments(16) Filed in category: Count values, Excel, Range

Comments(16) Filed in category: Count values, Excel, Frequency

Count records between two dates with multiple parameters in excel

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]Comments(14) Filed in category: Count values, Dates, Excel

Count unique distinct values in two columns in excel

Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are […]Comments(14) Filed in category: Count unique distinct values, Count values, Excel

Count occurences of a specific text string in a column in excel

Question: How do I count the number of times a text string exists in a column? Answer: Array formula in […]Comments(9) Filed in category: Count text values, Count values, Excel

Count text that occurs multiple times in excel cell

Question: How do I count how many times a text string occurs in a cell in excel? Answer: Formula in […]Comments(9) Filed in category: Count text values, Excel