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
Mary22  
Devin13  
Shannon56  
Robert65  
Alison31  
Corey44  

Sheet 2 ( Week 1)

-

Employee NameSide A or BAvg Heart RateTotal MovementTotal Boxes MovedTime of Sign OutIndoor Temp
DevinA     
Day 1 761032 75
Day 2 76729275
MaryA     
Day 1 751237 76
Day 2 881335476
AlisonB     
Day 1 771037 76
Day 2 881135676
CoreyB     
Day 1 691245 76
Day 2 73925776
ShannonA     
Day 1 721043 75
Day 2 81845775
RobertB     
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
AlisonA     
Day 1 76633 75
Day 2 69928275
MaryA     
Day 1 811134 76
Day 2 75437476
DevinB     
Day 1 78938 76
Day 2 901434678
CoreyB     
Day 1 771142 78
Day 2 761347776
RobertA     
Day 1 791041 75
Day 2 811039776
ShannonB     
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

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