Sep 10 2021 11:49 PM - edited Sep 10 2021 11:54 PM
Hey Everyone,
I'm having a really hard time writing a function in excel, not because of the logic, but rather the language.
I'm trying to create a function that will return back string.
I'd really like a function that does this
Function Create_Count_IF(Employee_Name_Range, Employee_Name, Measurement 1, Name_Range 2, Measurement 2.........Name_Range N, Name_Range Measurement N)
So in the example, I want to tally the total amount of weeks "Robert" was
-Working on side A
-Working in conditions equal to or hotter than 75
//Grab Roberts Row Positioning
Week 1 Week 2 Week 3 Week 4........... Week N
6 5
Create_Count_IF(Track!A2:A7, ="Robert", Week_1!B2:B19,="A",Week_1!G2:919,>=75)
returns string
CountIF( Week_1!B18:B19,="A",Week_1!G18:19,>=75) +CountIF( Week_2!B16:B17,="A",Week_2!G16:17,>=75)
There is currently 3 sheets in my example
Sheet 1 (Track)
-This just tells you which employee shows up in order from top to bottom in each week's spreadsheet.
Employee Name | Week 1 | Week 2 | Week 3 | Week 4 |
Mary | 2 | 2 | ||
Devin | 1 | 3 | ||
Shannon | 5 | 6 | ||
Robert | 6 | 5 | ||
Alison | 3 | 1 | ||
Corey | 4 | 4 |
Sheet 2 ( Week 1)
-
Employee Name | Side A or B | Avg Heart Rate | Total Movement | Total Boxes Moved | Time of Sign Out | Indoor Temp |
Devin | A | |||||
Day 1 | 76 | 10 | 32 | 75 | ||
Day 2 | 76 | 7 | 29 | 2 | 75 | |
Mary | A | |||||
Day 1 | 75 | 12 | 37 | 76 | ||
Day 2 | 88 | 13 | 35 | 4 | 76 | |
Alison | B | |||||
Day 1 | 77 | 10 | 37 | 76 | ||
Day 2 | 88 | 11 | 35 | 6 | 76 | |
Corey | B | |||||
Day 1 | 69 | 12 | 45 | 76 | ||
Day 2 | 73 | 9 | 25 | 7 | 76 | |
Shannon | A | |||||
Day 1 | 72 | 10 | 43 | 75 | ||
Day 2 | 81 | 8 | 45 | 7 | 75 | |
Robert | B | |||||
Day 1 | 87 | 12 | 33 | 75 | ||
Day 2 | 78 | 12 | 33 | 9 | 74 |
Spreadsheet 3( week 2)
Employee Name | Side A or B | Avg Heart Rate | Total Movement | Total Boxes Moved | Time of Sign Out | Indoor Temp |
Alison | A | |||||
Day 1 | 76 | 6 | 33 | 75 | ||
Day 2 | 69 | 9 | 28 | 2 | 75 | |
Mary | A | |||||
Day 1 | 81 | 11 | 34 | 76 | ||
Day 2 | 75 | 4 | 37 | 4 | 76 | |
Devin | B | |||||
Day 1 | 78 | 9 | 38 | 76 | ||
Day 2 | 90 | 14 | 34 | 6 | 78 | |
Corey | B | |||||
Day 1 | 77 | 11 | 42 | 78 | ||
Day 2 | 76 | 13 | 47 | 7 | 76 | |
Robert | A | |||||
Day 1 | 79 | 10 | 41 | 75 | ||
Day 2 | 81 | 10 | 39 | 7 | 76 | |
Shannon | B | |||||
Day 1 | 87 | 9 | 39 | 76 | ||
Day 2 | 78 | 12 | 38 | 9 | 74 |
Can you guys help me write this function? I've been having a really hard time following the rules.
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.
Oct 07 2021 01:00 PM
=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"))
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.
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).
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"}
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.
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.