# Thread: Finding the Min and assigning to that category Thanks: 0 Likes:  2 Post #5342895 (2)

1. ## Finding the Min and assigning to that category

I have a spreadsheet with 2 separate sheets set up as follows

List
 Department Shift State Home Health Nights Toys Nights Toys Days Sporting Goods Days Grocery Days Toys Nights Furniture Days Pets Nights

The goal of this is to look at the summary sheet below and populate the State column with the lowest value that matches the department and shift.

Summary sheet:
 Department Alabama Alabama Alaska Alaska Maryland Maryland Maine Maine Pets Days Nights Days Nights Days Nights Days Nights Grocery 5 6 9 4 2 34 8 6 Garden 5 879 4 5 62 154 87 52 Home Health 1567 79 712 3 456 78 152 12 Cosmetics 1 2 65 79 8 4891 54 568 Furniture 45 45 878 9 8 455 657 26 Toys 126 489 897 7891 324 348 48 9 Sporting Goods 87 98 7 1 56 4897 97 321

These aren't numbers on the summary sheet, they are a countifs statement taht counts the state column (C) from the summary sheet for that specific unit and shift. For example there are a count of 456 "Maryland" in column C on the summary sheet.

When a state is populated in the "List" Spreadsheet the formulas on the summary tab would add 1 to that department/state/Day field respectively. Therefor the next item in the list could be impacted from the previous item on the list.

Is this possible with a formula in the State cells on the List spreadsheet?

2. ## Re: Finding the Min and assigning to that category

Hi Magoos,

This looks like its working ok, Note that Pets doesn't have values so the formulas start from Grocery.

Also I made a extra column for the Min so I could see at a glance that is working correctly;

Here's a link to my mockup version as HTML seems to be off here so I can't post sample code...

https://www.dropbox.com/s/1nh2x0ydpx...ball.xlsx?dl=0

3. ## Re: Finding the Min and assigning to that category

Originally Posted by RasGhul
.. HTML seems to be off here so I can't post sample code...
Thread has now been moved to the correct forum (with HTML on) so it would be good if you did now post your suggestion here.

4. ## Re: Finding the Min and assigning to that category

@Peter_SSs thanks for moving sir..;

Hi Magoos,

This looks like its working ok, Note that Pets doesn't have values so the formulas start from Grocery.

Also I made a extra column for the Min so I could see at a glance that is working correctly;

ABCDEFGHI
2PeriodDaysNightsDaysNightsDaysNightsDaysNights
3Grocery569423486
4Garden587945621548752
5Home Health15677971234567815212
6Cosmetics1265798489154568
7Furniture45458789845565726
8Toys1264898977891324348489
9Sporting Goods87987156489797321

Summary

ABCD
1DepartmentShiftMinState
2CosmeticsDays1Alabama
3CosmeticsNights2Alabama
4FurnitureDays8Maryland
8GroceryDays2Maryland
10Home HealthDays152Maine
16ToysDays48Maine
17ToysNights9Maine

List

Worksheet Formulas
CellFormula
D2=INDEX(Summary!\$B\$1:\$I\$1,,SUMPRODUCT((Summary!\$A\$3:\$A\$9&Summary!\$B\$2:\$I\$2&Summary!\$B\$3:\$I\$9=A2&B2&C2)*(COLUMN(Summary!\$B\$1:\$I\$1)-COLUMN(Summary!\$A\$1))))

Array Formulas
CellFormula
C2{=MIN(IF(Summary!\$A\$3:\$A\$9&Summary!\$B\$2:\$I\$2=\$A2&\$B2,Summary!\$B\$3:\$I\$9))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself