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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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
Back
Top