Finding the Min and assigning to that category

Magoosball

Board Regular
Joined
Jun 4, 2017
Messages
55
I have a spreadsheet with 2 separate sheets set up as follows

List
Department
ShiftState
Home HealthNights
ToysNights
ToysDays
Sporting GoodsDays
GroceryDays
ToysNights
FurnitureDays
PetsNights

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
AlabamaAlabamaAlaskaAlaskaMarylandMarylandMaineMaine
Pets
DaysNightsDaysNightsDaysNightsDaysNights
Grocery569423486
Garden587945621548752
Home Health15677971234567815212
Cosmetics1265798489154568
Furniture45458789845565726
Toys1264898977891324348489
Sporting Goods87987156489797321

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?

Thank you in advance!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
[MENTION]Peter_SSs thanks for moving sir..[/MENTION]

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;

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #FFFFFF;background-color: #4472C4;;">Department</td><td style="color: #FFFFFF;background-color: #4472C4;;">Alabama</td><td style="color: #FFFFFF;background-color: #4472C4;;">Alabama</td><td style="color: #FFFFFF;background-color: #4472C4;;">Alaska</td><td style="color: #FFFFFF;background-color: #4472C4;;">Alaska</td><td style="color: #FFFFFF;background-color: #4472C4;;">Maryland</td><td style="color: #FFFFFF;background-color: #4472C4;;">Maryland</td><td style="color: #FFFFFF;background-color: #4472C4;;">Maine</td><td style="color: #FFFFFF;background-color: #4472C4;;">Maine</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Period</td><td style=";">Days</td><td style=";">Nights</td><td style=";">Days</td><td style=";">Nights</td><td style=";">Days</td><td style=";">Nights</td><td style=";">Days</td><td style=";">Nights</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Grocery</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">9</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">34</td><td style="text-align: right;;">8</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Garden</td><td style="text-align: right;;">5</td><td style="text-align: right;;">879</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">62</td><td style="text-align: right;;">154</td><td style="text-align: right;;">87</td><td style="text-align: right;;">52</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Home Health</td><td style="text-align: right;;">1567</td><td style="text-align: right;;">79</td><td style="text-align: right;;">712</td><td style="text-align: right;;">3</td><td style="text-align: right;;">456</td><td style="text-align: right;;">78</td><td style="text-align: right;;">152</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Cosmetics</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">65</td><td style="text-align: right;;">79</td><td style="text-align: right;;">8</td><td style="text-align: right;;">4891</td><td style="text-align: right;;">54</td><td style="text-align: right;;">568</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Furniture</td><td style="text-align: right;;">45</td><td style="text-align: right;;">45</td><td style="text-align: right;;">878</td><td style="text-align: right;;">9</td><td style="text-align: right;;">8</td><td style="text-align: right;;">455</td><td style="text-align: right;;">657</td><td style="text-align: right;;">26</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Toys</td><td style="text-align: right;;">126</td><td style="text-align: right;;">489</td><td style="text-align: right;;">897</td><td style="text-align: right;;">7891</td><td style="text-align: right;;">324</td><td style="text-align: right;;">348</td><td style="text-align: right;;">48</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Sporting Goods</td><td style="text-align: right;;">87</td><td style="text-align: right;;">98</td><td style="text-align: right;;">7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">56</td><td style="text-align: right;;">4897</td><td style="text-align: right;;">97</td><td style="text-align: right;;">321</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Summary</p><br /><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #FFFFFF;background-color: #ED7D31;;">Department</td><td style="color: #FFFFFF;background-color: #ED7D31;;">Shift</td><td style="text-align: center;color: #FFFFFF;background-color: #ED7D31;;">Min</td><td style="text-align: center;color: #FFFFFF;background-color: #ED7D31;;">State</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Cosmetics</td><td style=";">Days</td><td style="text-align: center;;">1</td><td style="text-align: center;;">Alabama</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Cosmetics</td><td style=";">Nights</td><td style="text-align: center;;">2</td><td style="text-align: center;;">Alabama</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Furniture</td><td style=";">Days</td><td style="text-align: center;;">8</td><td style="text-align: center;;">Maryland</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Furniture</td><td style=";">Nights</td><td style="text-align: center;;">9</td><td style="text-align: center;;">Alaska</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Garden</td><td style=";">Days</td><td style="text-align: center;;">4</td><td style="text-align: center;;">Alaska</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Garden</td><td style=";">Nights</td><td style="text-align: center;;">5</td><td style="text-align: center;;">Alaska</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Grocery</td><td style=";">Days</td><td style="text-align: center;;">2</td><td style="text-align: center;;">Maryland</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Grocery</td><td style=";">Nights</td><td style="text-align: center;;">4</td><td style="text-align: center;;">Alaska</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Home Health</td><td style=";">Days</td><td style="text-align: center;;">152</td><td style="text-align: center;;">Maine</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Home Health</td><td style=";">Nights</td><td style="text-align: center;;">3</td><td style="text-align: center;;">Alaska</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Pets</td><td style=";">Days</td><td style="text-align: center;;">0</td><td style="text-align: center;;">Alaska</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Pets</td><td style=";">Nights</td><td style="text-align: center;;">0</td><td style="text-align: center;;">Alaska</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Sporting Goods</td><td style=";">Days</td><td style="text-align: center;;">7</td><td style="text-align: center;;">Alaska</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Sporting Goods</td><td style=";">Nights</td><td style="text-align: center;;">1</td><td style="text-align: center;;">Alaska</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Toys</td><td style=";">Days</td><td style="text-align: center;;">48</td><td style="text-align: center;;">Maine</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Toys</td><td style=";">Nights</td><td style="text-align: center;;">9</td><td style="text-align: center;;">Maine</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">List</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=INDEX(<font color="Blue">Summary!$B$1:$I$1,,SUMPRODUCT(<font color="Red">(<font color="Green">Summary!$A$3:$A$9&Summary!$B$2:$I$2&Summary!$B$3:$I$9=A2&B2&C2</font>)*(<font color="Green">COLUMN(<font color="Purple">Summary!$B$1:$I$1</font>)-COLUMN(<font color="Purple">Summary!$A$1</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">Summary!$A$3:$A$9&Summary!$B$2:$I$2=$A2&$B2,Summary!$B$3:$I$9</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,143
Messages
5,466,931
Members
406,511
Latest member
markflayd

This Week's Hot Topics

Top