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

[JeremyIsADeadHead]

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. 

[#2]

@JeremyIsADeadHead 

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