Forum Discussion

JeremyIsADeadHead's avatar
JeremyIsADeadHead
Copper Contributor
Sep 11, 2021

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. ...
excel
Formulas and Functions
Macros and VBA
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 07, 2021

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.

Resources