# Inventory Management with COUNTIF / VLOOKUP

Dewey Source

Using `COUNTIF` and `VLOOKUP`, the goal is to count my inventory of Laptops and return the number I have in stock.

My current formula is this:

``````=COUNTIF(\$A\$3:\$A\$10000,H4)
``````

As my table only has one entry for "Laptop", the formula evaluates to 1.

The next step was to incorporate some sort of `VLOOKUP` for quantity. As an example, Row 17 has an entry for "Monitor" with a specific model, with a column E (quantity) value of 5:

`````` Monitor        HP Compaq LA1951G   1FL SVR 5
``````

I could use a `VLOOKUP` here:

``````=VLOOKUP(H6,\$A\$3:\$E\$10000,5,FALSE)
``````

However, I have a second set of monitors with a different model. From what I understand of `VLOOKUP`, the formula stops evaluating at the first match.

I'm considering simply listing every possible model in our environment and strictly using `VLOOKUP` only, but this makes my life more complicated.

Any suggestions on a better way of doing this before I do a lot of manual work would be appreciated. I've linked a sanitized copy of my workbook via Google Sheets here.

excelexcel-formulaworksheet-function

### Answers

answered 1 week ago shash #1

I believe the `SUMIF()` function would be more useful here.

Replace the

``````=COUNTIF(\$A\$3:\$A\$10000,H4)
``````

with

``````=SUMIF(A:E,H4,E:E)
``````

answered 1 week ago C.A.R. #2

Having done this same process for many companies, I suggest you also consider a Pivot Table. It can automatically update, auto-sum everything, and auto-count everything if you've set it up properly.

Pivot Tables also work in Excel and Sheets equally well. Just make sure that you're defining entire Columns of data for the basis of the data sets and it will be dynamic! E.G. Don't do A1:F50, do A:F for your data set definitions.

comments powered by Disqus