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

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

### Related posts:

Categorize values into multiple columns (excel formulas)

Categorize values into multiple columns using vba in excel

Categorize data entry values (vba)

Excel: Using lookup to categorize numbers

Select 6 unique and random numbers from 1 to 49 (Lottery) in excel