*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!!*

*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!*

*---------------------------------------------------------------------------------------------------------------------------------------*