Discussion Re: Help With Language for Function (FIRST POST!!!!) in Excel
https://techcommunity.microsoft.com/t5/excel/help-with-language-for-function-first-post/m-p/2824193#M117146
<P><LI-USER uid="1152813"></LI-USER> </P><P>=SUMPRODUCT(COUNTIFS(INDIRECT("week"&ROW($1:$3)&"!H3:H21"),">=75",INDIRECT("week"&ROW($1:$3)&"!A2:A20"),A2,INDIRECT("week"&ROW($1:$3)&"!B2:B20"),"B"))</P><P> </P><P>I suggest to apply above formula to count the number of weeks when Robert worked at side B and when temperature was >=75. Enter formula in F2 of sheet "task" and copy down.</P><P> </P><P>In order to check the temperature criteria i added an additional column (H in this example, please see attached file) and entered a MAX formula to calculate the highest temperature during the week (in your example Day 1 and Day 2).</P><P> </P><P>Above formula works for 3 sheets already. The reference of the indirect function ("week"&ROW($1:$3)&"!H3:H21") refers to ranges: {"week1!H3:H21";"week2!H3:H21";"week3!H3:H21"}</P><P> </P><P>This means that formula can easily be adapted to calculate through many more sheets, e.g. by entering: "week"&ROW($1:$100)&"!H3:H21" and the equivalents in the search ranges of above formula.</P><P> </P><P>Of course there are ways to carry out calculation for week1 to 9 and week 12 and week15 in one go. However this would require some adjustments. It is as well possible to make the formula dynamic, e.g. if you want to replace A with B or 75 with 77 or if the number of employees changes.</P>Thu, 07 Oct 2021 20:00:19 GMTQuadruple_Pawn2021-10-07T20:00:19ZHelp With Language for Function (FIRST POST!!!!)
https://techcommunity.microsoft.com/t5/excel/help-with-language-for-function-first-post/m-p/2741975#M113526
<P>Hey Everyone,</P><P> </P><P>I'm having a really hard time writing a function in excel, not because of the logic, but rather the language. </P><P> </P><P>I'm trying to create a function that will return back string.<BR /><BR /></P><P>I'd really like a function that does this</P><P> </P><P>Function Create_Count_IF(Employee_Name_Range, Employee_Name, Measurement 1, Name_Range 2, Measurement 2.........Name_Range N, Name_Range Measurement N)</P><P> </P><P> </P><P>So in the example, I want to tally the total amount of weeks "Robert" was</P><P> </P><P>-Working on side A</P><P>-Working in conditions equal to or hotter than 75</P><P> </P><P>//Grab Roberts Row Positioning </P><P>Week 1 Week 2 Week 3 Week 4........... Week N</P><P><FONT color="#FF6600">6 5 </FONT></P><P> </P><P> </P><P><FONT color="#000000">Create_Count_IF(Track!A2:A7, ="Robert", Week_1!B2:B19,="A",Week_1!G2:919,>=75)</FONT></P><P> </P><P>returns string</P><P> </P><P><FONT color="#000000">CountIF( Week_1!B18:B19,="A",Week_1!G18:19,>=75) +CountIF( Week_2!B16:B17,="A",Week_2!G16:17,>=75)</FONT></P><P> </P><P> </P><P> </P><P> </P><P> </P><P>There is currently 3 sheets in my example</P><P> </P><P>Sheet 1 (Track)</P><P>-This just tells you which employee shows up in order from top to bottom in each week's spreadsheet. </P><TABLE width="385"><TBODY><TR><TD width="129">Employee Name</TD><TD width="64">Week 1</TD><TD width="64">Week 2</TD><TD width="64">Week 3</TD><TD width="64">Week 4</TD></TR><TR><TD>Mary</TD><TD>2</TD><TD>2</TD><TD> </TD><TD> </TD></TR><TR><TD>Devin</TD><TD>1</TD><TD>3</TD><TD> </TD><TD> </TD></TR><TR><TD>Shannon</TD><TD>5</TD><TD>6</TD><TD> </TD><TD> </TD></TR><TR><TD>Robert</TD><TD>6</TD><TD>5</TD><TD> </TD><TD> </TD></TR><TR><TD>Alison</TD><TD>3</TD><TD>1</TD><TD> </TD><TD> </TD></TR><TR><TD>Corey</TD><TD>4</TD><TD>4</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P>Sheet 2 ( Week 1)</P><P>-</P><TABLE width="798px"><TBODY><TR><TD width="135px">Employee Name</TD><TD width="79px">Side A or B</TD><TD width="102px">Avg Heart Rate</TD><TD width="123px">Total Movement</TD><TD width="135px">Total Boxes Moved</TD><TD width="123px">Time of Sign Out</TD><TD width="101px">Indoor Temp</TD></TR><TR><TD width="135px">Devin</TD><TD width="79px">A</TD><TD width="102px"> </TD><TD width="123px"> </TD><TD width="135px"> </TD><TD width="123px"> </TD><TD width="101px"> </TD></TR><TR><TD width="135px">Day 1</TD><TD width="79px"> </TD><TD width="102px">76</TD><TD width="123px">10</TD><TD width="135px">32</TD><TD width="123px"> </TD><TD width="101px">75</TD></TR><TR><TD width="135px">Day 2</TD><TD width="79px"> </TD><TD width="102px">76</TD><TD width="123px">7</TD><TD width="135px">29</TD><TD width="123px">2</TD><TD width="101px">75</TD></TR><TR><TD width="135px">Mary</TD><TD width="79px">A</TD><TD width="102px"> </TD><TD width="123px"> </TD><TD width="135px"> </TD><TD width="123px"> </TD><TD width="101px"> </TD></TR><TR><TD width="135px">Day 1</TD><TD width="79px"> </TD><TD width="102px">75</TD><TD width="123px">12</TD><TD width="135px">37</TD><TD width="123px"> </TD><TD width="101px">76</TD></TR><TR><TD width="135px">Day 2</TD><TD width="79px"> </TD><TD width="102px">88</TD><TD width="123px">13</TD><TD width="135px">35</TD><TD width="123px">4</TD><TD width="101px">76</TD></TR><TR><TD width="135px">Alison</TD><TD width="79px">B</TD><TD width="102px"> </TD><TD width="123px"> </TD><TD width="135px"> </TD><TD width="123px"> </TD><TD width="101px"> </TD></TR><TR><TD width="135px">Day 1</TD><TD width="79px"> </TD><TD width="102px">77</TD><TD width="123px">10</TD><TD width="135px">37</TD><TD width="123px"> </TD><TD width="101px">76</TD></TR><TR><TD width="135px">Day 2</TD><TD width="79px"> </TD><TD width="102px">88</TD><TD width="123px">11</TD><TD width="135px">35</TD><TD width="123px">6</TD><TD width="101px">76</TD></TR><TR><TD width="135px">Corey</TD><TD width="79px">B</TD><TD width="102px"> </TD><TD width="123px"> </TD><TD width="135px"> </TD><TD width="123px"> </TD><TD width="101px"> </TD></TR><TR><TD width="135px">Day 1</TD><TD width="79px"> </TD><TD width="102px">69</TD><TD width="123px">12</TD><TD width="135px">45</TD><TD width="123px"> </TD><TD width="101px">76</TD></TR><TR><TD width="135px">Day 2</TD><TD width="79px"> </TD><TD width="102px">73</TD><TD width="123px">9</TD><TD width="135px">25</TD><TD width="123px">7</TD><TD width="101px">76</TD></TR><TR><TD width="135px">Shannon</TD><TD width="79px">A</TD><TD width="102px"> </TD><TD width="123px"> </TD><TD width="135px"> </TD><TD width="123px"> </TD><TD width="101px"> </TD></TR><TR><TD width="135px">Day 1</TD><TD width="79px"> </TD><TD width="102px">72</TD><TD width="123px">10</TD><TD width="135px">43</TD><TD width="123px"> </TD><TD width="101px">75</TD></TR><TR><TD width="135px">Day 2</TD><TD width="79px"> </TD><TD width="102px">81</TD><TD width="123px">8</TD><TD width="135px">45</TD><TD width="123px">7</TD><TD width="101px">75</TD></TR><TR><TD width="135px">Robert</TD><TD width="79px">B</TD><TD width="102px"> </TD><TD width="123px"> </TD><TD width="135px"> </TD><TD width="123px"> </TD><TD width="101px"> </TD></TR><TR><TD width="135px">Day 1</TD><TD width="79px"> </TD><TD width="102px">87</TD><TD width="123px">12</TD><TD width="135px">33</TD><TD width="123px"> </TD><TD width="101px">75</TD></TR><TR><TD width="135px">Day 2</TD><TD width="79px"> </TD><TD width="102px">78</TD><TD width="123px">12</TD><TD width="135px">33</TD><TD width="123px">9</TD><TD width="101px">74</TD></TR></TBODY></TABLE><P> </P><P> </P><P>Spreadsheet 3( week 2)</P><P> </P><TABLE width="755"><TBODY><TR><TD width="64">Employee Name</TD><TD width="111">Side A or B</TD><TD width="119">Avg Heart Rate</TD><TD width="119">Total Movement</TD><TD width="129">Total Boxes Moved</TD><TD width="99">Time of Sign Out</TD><TD width="114">Indoor Temp</TD></TR><TR><TD>Alison</TD><TD>A</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD width="64">Day 1</TD><TD> </TD><TD>76</TD><TD>6</TD><TD>33</TD><TD> </TD><TD>75</TD></TR><TR><TD width="64">Day 2</TD><TD> </TD><TD>69</TD><TD>9</TD><TD>28</TD><TD>2</TD><TD>75</TD></TR><TR><TD>Mary</TD><TD>A</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD width="64">Day 1</TD><TD> </TD><TD>81</TD><TD>11</TD><TD>34</TD><TD> </TD><TD>76</TD></TR><TR><TD width="64">Day 2</TD><TD> </TD><TD>75</TD><TD>4</TD><TD>37</TD><TD>4</TD><TD>76</TD></TR><TR><TD>Devin</TD><TD>B</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD width="64">Day 1</TD><TD> </TD><TD>78</TD><TD>9</TD><TD>38</TD><TD> </TD><TD>76</TD></TR><TR><TD width="64">Day 2</TD><TD> </TD><TD>90</TD><TD>14</TD><TD>34</TD><TD>6</TD><TD>78</TD></TR><TR><TD>Corey</TD><TD>B</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD width="64">Day 1</TD><TD> </TD><TD>77</TD><TD>11</TD><TD>42</TD><TD> </TD><TD>78</TD></TR><TR><TD width="64">Day 2</TD><TD> </TD><TD>76</TD><TD>13</TD><TD>47</TD><TD>7</TD><TD>76</TD></TR><TR><TD>Robert</TD><TD>A</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD width="64">Day 1</TD><TD> </TD><TD>79</TD><TD>10</TD><TD>41</TD><TD> </TD><TD>75</TD></TR><TR><TD width="64">Day 2</TD><TD> </TD><TD>81</TD><TD>10</TD><TD>39</TD><TD>7</TD><TD>76</TD></TR><TR><TD>Shannon</TD><TD>B</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD width="64">Day 1</TD><TD> </TD><TD>87</TD><TD>9</TD><TD>39</TD><TD> </TD><TD>76</TD></TR><TR><TD width="64">Day 2</TD><TD> </TD><TD>78</TD><TD>12</TD><TD>38</TD><TD>9</TD><TD>74</TD></TR></TBODY></TABLE><P> </P><P>Can you guys help me write this function? I've been having a really hard time following the rules. </P><P> </P><P>Also, I understand I can send back a value instead of a string, but I may want to manually omit a week or slightly change the conditions for another week. </P>Sat, 11 Sep 2021 06:54:59 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-language-for-function-first-post/m-p/2741975#M113526JeremyIsADeadHead2021-09-11T06:54:59ZRe: Help With Language for Function (FIRST POST!!!!)
https://techcommunity.microsoft.com/t5/excel/help-with-language-for-function-first-post/m-p/2824193#M117146
<P><LI-USER uid="1152813"></LI-USER> </P><P>=SUMPRODUCT(COUNTIFS(INDIRECT("week"&ROW($1:$3)&"!H3:H21"),">=75",INDIRECT("week"&ROW($1:$3)&"!A2:A20"),A2,INDIRECT("week"&ROW($1:$3)&"!B2:B20"),"B"))</P><P> </P><P>I suggest to apply above formula to count the number of weeks when Robert worked at side B and when temperature was >=75. Enter formula in F2 of sheet "task" and copy down.</P><P> </P><P>In order to check the temperature criteria i added an additional column (H in this example, please see attached file) and entered a MAX formula to calculate the highest temperature during the week (in your example Day 1 and Day 2).</P><P> </P><P>Above formula works for 3 sheets already. The reference of the indirect function ("week"&ROW($1:$3)&"!H3:H21") refers to ranges: {"week1!H3:H21";"week2!H3:H21";"week3!H3:H21"}</P><P> </P><P>This means that formula can easily be adapted to calculate through many more sheets, e.g. by entering: "week"&ROW($1:$100)&"!H3:H21" and the equivalents in the search ranges of above formula.</P><P> </P><P>Of course there are ways to carry out calculation for week1 to 9 and week 12 and week15 in one go. However this would require some adjustments. It is as well possible to make the formula dynamic, e.g. if you want to replace A with B or 75 with 77 or if the number of employees changes.</P>Thu, 07 Oct 2021 20:00:19 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-language-for-function-first-post/m-p/2824193#M117146Quadruple_Pawn2021-10-07T20:00:19Z