## 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 a given value

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 […]

Count unique distinct values in two columns

Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Count unique distinct values in a filtered table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]

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 […]

Count unique distinct text values in a range

Array formula in D14: =SUM(IF(FREQUENCY(COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")), COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")))>0, 1, 0)) + CTRL + SHIFT + ENTER […]

Count unique distinct records with a date and column criteria

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form