How does this formula work? (SUMPRODUCT(LOOKUP(ROW(INDIRECT(C$4&":"&EOMONTH(C$4,0))

INDIRECT(C$4&":"&EOMONTH(C$4,0)) will turn into a range of rows based on the numeric value of the date in C4 and the End of Month date of C4. For 2/1/2012 it would be INDIRECT("40940:40968") from there we end up with ROW("40940:40968")+6, which will give us a range of "40946:40974" to use as our LOOKUP value (imagine each value from 40946 to 40974 listed as an array). The LOOKUP vector is 'Sheet CJNH 2012'!3:3 which we know contains 2/4/12, 2/11/12, 2/18/12, 2/25/12, 3/3/12. Those equate to number values of 40943, 40950 ,40957, 40964, 40971. If the LOOKUP function can't find the LOOKUP value, the function matches the largest value in LOOKUP vector that is less than or equal to LOOKUP value. So in the array {40946, 40947, 40948, 40949, 40950, 40951, 40952, 40953, 40954, 40955, 40956, 40957, 40958, 40959, 40960, 40961, 40962, 40963, 40964, 40965, 40966, 40967, 40968, 40969, 40970, 40971, 40972, 40973, 40974} 40946 finds 40943 (date value of 2/4/2012); 40947 finds 40943; 40948 finds 40943; and 40949 finds 40943. Because your LOOKUP result is 'Sheet CJNH 2012'!155:155, it will use the data from that row (in the corresponding column to row 3). If the value in the column where 2/4/2012 intersects row 155 is 1, then this part of the formula would result in 4 (1+1+1+1). If you keep moving down the long array list (we're at 40950); 40950 finds 40950; 40951 finds 40950; 40952 finds 40950; 40953 finds 40950; 40954 finds 40950; 40955 finds 40950; and 40956 finds 40950. Again if the values in row 155 were 1, you'd result in 7 (1+1+1+1+1+1+1). If you keep going through the long array list and adding the values from row 155 and then divide the total sum by 7, you should get the result from the formula you posted.

Hopefully this helps a bit