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