Multiple Condition Minimum Formula

RKN

New Member
Joined
May 5, 2010
Messages
20
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.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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.
 

alvinwlh

Active Member
Joined
Feb 16, 2009
Messages
305
Hi,

another alternative:

for cell at D2
array formula:
Code:
=MIN(IF(IF(C:C=C2,B:B)<>0,IF(C:C=C2,B:B)))
 
Last edited:

RKN

New Member
Joined
May 5, 2010
Messages
20
Thank you very much! With array formulas how do i make the "C2" reference in the formula below automatically change to C3...C4...C5..etc. when I apply/paste the formula to subsequent rows?
 

RKN

New Member
Joined
May 5, 2010
Messages
20

ADVERTISEMENT

Sorry for the last question...not too familiar with array formulas. I think I figured it out....do the array formula in the first cell and then just drag it to paste. I was highlighting the whole range, inputting the array formula in the formula bar and then pressed control-shift-enter.

Thanks everyone!
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

Thank you very much! With array formulas how do i make the "C2" reference in the formula below automatically change to C3...C4...C5..etc. when I apply/paste the formula to subsequent rows?
Enter the array formula in a single cell (not an array of cells). Then, as you drag copy the formula down the column the cell reference to C2 will increment as needed.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Sorry for the last question...not too familiar with array formulas. I think I figured it out....do the array formula in the first cell and then just drag it to paste. I was highlighting the whole range, inputting the array formula in the formula bar and then pressed control-shift-enter.

Thanks everyone!
Yes, that's how you want to do it.

Glad you got it straightened out! :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,077
Members
414,500
Latest member
kevdragon1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top