Negative Inventory Items

[MrSamuel123]

I need a formula that returns all inventory items in Column A if any of the items have a negative balance in Column F. In other words, Widget #1 has 10 line items in a data set but only one of them has a negative balance. I want to see all ten line items but none of the other widgets that have all positive balances in all locations.

 

[StephenCrump]

Welcome to the Forum!

I'd be inclined to set a simple flag and filter the results:

	A	B	C	D	E	F	G
1	Code					Amount	Flag
2	A					10	FALSE
3	A					10	FALSE
4	A					10	FALSE
5	B					10	TRUE
6	B					-20	TRUE
7	B					10	TRUE
8	C					10	FALSE
9	C					10	FALSE
10	C					10	FALSE
11	C					10	FALSE
12	C					10	FALSE
Sheet1

Cell Formulas
Range	Formula
G2:G12	G2	=MINIFS(F$2:F$12,A$2:A$12,A2)<0

After filtering:

	A	B	C	D	E	F	G
1	Code					Amount	Flag
5	B					10	TRUE
6	B					-20	TRUE
7	B					10	TRUE
Sheet1

Cell Formulas
Range	Formula
G5:G7	G5	=MINIFS(F$2:F$12,A$2:A$12,A5)<0

A formula approach is also possible, but would be more complicated.

 

[MrSamuel123]

Thanks for your help! I copied the formula and adjusted the ranges. All of the calculated cells are returning FALSE. Any ideas on why that would be?

 

[MrSamuel123]

I figured it out. Thanks a lot for your solution! It's saving all kinds of time.

 

[StephenCrump]

I figured it out. Thanks a lot for your solution! It's saving all kinds of time.

Great! Thanks for letting us know.