New Contributor

# Help With Language for Function (FIRST POST!!!!)

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

 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.

# Re: Help With Language for Function (FIRST POST!!!!)

=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.