## Categorize numbers in excel

*Article updated on February 23, 2009*

**Question:** How to categorize numbers in excel? Like age into 0>5, 6>10, 11>15 and so on?

**Answer:**

In cell D1 =SUM(IF(C1>=$A$1:$A$35,1,0))** **+ Ctrl + Shift + Enter

In cell D2:D16, =SUM(IF((C2>=$A$1:$A$35)*(C1<$A$1:$A$35),1,0)) + Ctrl + Shift + Enter

Let us calculate the value in cell D2: IF((C2>=$A$1:$A$35)*(C1<$A$1:$A$35),1,0) gives us two arrays {0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1}*{1,1,1,1,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0) equals {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

SUM(IF((C2>=$A$1:$A$35)*(C1<$A$1:$A$35),1,0)) sums {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0} = 1

Download excel sample file for this tutorial. categorize-numbers.xls (Excel 97-2003 Workbook *.xls)

**SUM(**number1,[number2],>**)
**Adds all the numbers in a range of cells

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 2 Responses to “Categorize numbers in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

=SUM(IF(c1>=$A$1:$A$35;1;0))+ control shift enter

comma's did not work

That depends on what your regional settings are. See this post: https://www.get-digital-help.com/2007/11/08/excel-regional-settings/