yogi_Compute Bonus For Members Of Teams On Specified Week By Week Basis

                                           Google Spreadsheet   Post  #1949
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-24, 2015
question by Jeff Underhill:

https://productforums.google.com/forum/#!mydiscussions/docs/0X8eG6WRUvY
Countif sum in date range between two sheets

Background: I'm a teacher gamifying my classroom and helping students learn to be prepared. Your help is appreciated by me and my 15 fifth graders!!

Copy of the sheet: https://docs.google.com/spreadsheets/d/1MG5QZfBCos1UzSUE9CQtpHjGNstLxxa53p4JMiRzjpk/edit?usp=sharing

Formula Needed: I'm attempting to count variables p, l, and u (p = prepared; l = late; u = unprepared) in sheet Q4 by weeks on sheet Weekly Bonus XP. I need the formula to be on sheet Weekly Bonus XP in each cell, beginning with cell H2 through V12. I need the new formula to use this formula from sheet Q4

=COUNTIF(C4:C, "p")*5+COUNTIF(C4:C, "e")*5+COUNTIF(C4:C, "l")*3+COUNTIF(C4:C, "u")*1

in an array formula, summing the above variables based on dates. The formula would use the date from sheet Q4 in column A and match it to the range between Monday and Friday on sheet Weekly Bonus XP in rows C through G.

For example, for each p, l, or u David earns in column C between March 31 and April 3 on sheet Q4, the formula on sheet Weekly Bonus XP would sum those values in the week March 31 - April 3 in cell H2.

I know precisely what I'm trying to accomplish and have tried many variations, to no avail. I am studying the syntax, and your help would be very appreciated!!

---

Hi Yogi,

I'm attempting to count the p, l, and u variables in sheet Q4 based on dates and sum them in a cell, per week and student, on sheet Weekly Bonus XP. For each student name in Q4, I need to count up the p, l, and u variables in that student's column IF those variables fall in a date range.

I need a formula on sheet Weekly Bonus XP in cell H2.

I need that formula to look something like =arrayformula(COUNTIF(C4:C, "p")*5+COUNTIF(C4:C, "e")*5+COUNTIF(C4:C, "l")*3+COUNTIF(C4:C, "u")*1)sum(date...))

p = 5 points

e = 5 points

l = 3 points

u = 1 point

So sheet Q4 column C variables will be summed for the week for the student on Sheet Weekly Bonus XP cell H2. That is, IF the date in sheet Q4 column C matches the range of dates in sheet Weekly Bonus XP between columns C2 and G2. The only dates needed in the formula would be C2 and G2, I imagine.

I would like the formula to be reiterative for each student and week.

Does this help? Appreciate your reply!

---------------------------------------------------------------------------------------------------------------------------------------