Formula

Josh Canady

Capture.PNG

I'm hoping someone can help me out here. I'm looking for a formula to calculate how many items have been sold but have not been shipped. Example:

I want it to add the #s in the Color column of all lines where WH Location = 8A-8Z unless "Shipped" is in the Vendor column.

Paul Newcome

Try this:

=COUNTIFS([WH Location]:[WH Location], @cell = "8A", Vendor:Vendor, @cell <> "Shipped")

Josh Canady

Almost, but it needs to count the totals in the Color column.

Paul Newcome

=SUMIFS(Color:Color, [WH Location]:[WH Location], @cell = "8A", Vendor:Vendor, @cell <> "Shipped")

Josh Canady

It appears the formula is working but it always equals zero. Even if i change the WH Location # it still equals zero.

I updated it to 50 below and it equals zero.

Paul Newcome

How exactly is the Color column being populated? What is the exact formula you have in that most recent screenshot?

Josh Canady

=SUMIFS(Color:Color, [WH Location]:[WH Location], @cell = "50", Vendor:Vendor, @cell <> "Shipped")

Josh Canady

Stock is removed through a form and that color cell is typed in.

Paul Newcome

How is the WH Location column populated? It looks like you have some that are numbers (50) and others that are text strings (2K).

Josh Canady

It is inputted in the form as well. I was hoping to edit the formula as needed for different line items.

I should be able to change the 50 to 2K or whatever the other lines are.

Paul Newcome

Ok. There may lie the problem then. You have to have like data in all of the cell. If some contain numerical data and others contain text, it can throw things off. Try inserting a (hidden) helper column that converts that entire column into text values:

=[WH Location]@row + ""

Then reference this helper column in your SUMIFS.