# How to formulate and gives a result based on the category i selected

#### Rock2

##### New Member
Here is the situation:
I have an entire data sheet that contains details with top column title: [Category] [Item Code] [Brand] [Location Bin] [Qty]
Under my Category list, i have various types: "shoes", "bag", "jacket", "gloves", "jeans" followed by the corresponding Item Code, Brand, Location Bin, Qty.
Note that i only have 3 Brands in my store (hypothetically), PUMA, NIKE and ADDIDAS.

Now i have set a Category drop down list on cell C15.
then a default text on D18 is Puma and C18 is a formula count of Puma, D19 is Nike and C19 is a formula count of Nike , D20 is Addidas and C20 is a formula count of Addidas,.

How to write a formula that gives me a total count of Brand based on my Category?
For example in excel: I chose "shoes" under my category drop down list on C15.
Then C18, C19, C20 should give me a result automaticaly of the total count of shoes i do have for Puma, Nike and Addidas.
This will help me identify where are they located on my inventory (since staff here just put it everwhere)

Heeelp! My brain is about to explode, struggling for 3 days now and inventory starts this week.

Really appreciate your guidance or support, apologees if it was already threaded, but i couldnt find it exactly where.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### seekerarcane

##### Board Regular
Use SUMIF() function to count the numbers based on your criteria,

For Example, if you want to sum the number of shoes, by selecting the category in an array, then use the following following formula;

=SUMIF(Range,Criteria,Sum_Range)
=SUMIF(Cells reference of Category Column,"Shows",Cells Range for Shows column)

Last edited by a moderator:

#### AlanY

##### Well-known Member
try this in D18 and copy down

Excel 2012
ABCDE
1CategoryItem CodeBrandLocation BinQty
3shoesNIKE7
4shoesNIKE6
5jacketPUMA9
6bagPUMA6
7glovesNIKE9
8bagNIKE9
9shoesPUMA6
10glovesNIKE6
12glovesNIKE8
14
15shoes
16
17
18PUMA6
19NIKE13
Sheet3
Cell Formulas
RangeFormula
D18=SUMIFS(\$E\$2:\$E\$13,\$A\$2:\$A\$13,\$C\$15,\$C\$2:\$C\$13,\$C18)

Replies
5
Views
99
Replies
13
Views
942
Replies
6
Views
200
Replies
0
Views
291
Replies
4
Views
297

1,191,691
Messages
5,988,113
Members
440,126
Latest member
duque00

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back