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

New Contributor

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 NameWeek 1Week 2Week 3Week 4

Sheet 2 ( Week 1)


Employee NameSide A or BAvg Heart RateTotal MovementTotal Boxes MovedTime of Sign OutIndoor Temp
Day 1 761032 75
Day 2 76729275
Day 1 751237 76
Day 2 881335476
Day 1 771037 76
Day 2 881135676
Day 1 691245 76
Day 2 73925776
Day 1 721043 75
Day 2 81845775
Day 1 871233 75
Day 2 781233974



Spreadsheet 3( week 2)


Employee NameSide A or BAvg Heart RateTotal MovementTotal Boxes MovedTime of Sign OutIndoor Temp
Day 1 76633 75
Day 2 69928275
Day 1 811134 76
Day 2 75437476
Day 1 78938 76
Day 2 901434678
Day 1 771142 78
Day 2 761347776
Day 1 791041 75
Day 2 811039776
Day 1 87939 76
Day 2 781238974


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. 

1 Reply




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.