I have a list of items with values assigned to multiple cost centers. I need to find the minimum item value listed for each cost center and have the minimum value listed in the same row for each item on the list. The formula for the minimum value needs to ignore zero values. The list is as follows:

__ItemID__ __Value__ __CostCenter__ __MinValueForCostCenter__

XXXXX 5 10 2

YYYYY 2 10 2

ZZZZZ 0 10 2

AAAAA 3 20 1

BBBBB 0 20 1

CCCCC 1 20 1

I assume this requires an array formula, but I have not been able to make one work. Any help anyone could provide would be much appreciated!

Thank you.

Try this array formula**:

=MIN(IF(C$2:C$7=C2,IF(B$2:B$7>0,B$2:B$7)))

** array formulas need to be entered using the key

combination of CTRL,SHIFT,ENTER (not just ENTER).

Hold down both the CTRL key and the SHIFT key

then hit ENTER.

Copy down as needed.

Note that if the max value for a cost center is 0 then the formula will return 0.