Help using SUMIF with a Variable

[L3Coronati]

Hello,
I have been trying to use a SUMIF statement in EXCEL 2000 to do some calculations from a pivot-table. Attached is the snapshot of the file being used. Column A is the weeks, and based on a formula (cell E1) =weeknum(now())- I want to sum all the hours in column C. The formula that works is the following:

= SUMIF(A6:A58,"<51",C6:C58). When I substitute <$E$1 the value is 0. I tried using SUM(INDEX and SUM(If statement) to no avail.

Any help is appreciated.

YEAR 2005 51 wk
4 15 1 6 2
Sum of RT_HR RESOURCE_ID
WEEKNUM COMSECTEST DASASSY DASRW DASTEST ENVIROMENTAL
35 549 1813 0 1436 0
38
40 22 4 0 5
41 0 39 0 216
42 583 325 0 194
43 15 113 0 22 1279
44 30 89 0 12 540
45 277 0 3 758
46 55 0 56 2
47 155 0 7 599
48 10 32 0
49 18 98 0 1
50 410 1591 0 42 2
51 4 0 2
52 5 0 3
53 16 51 0 8

 

[Norie]

Try this

= SUMIF(A6:A58,"<" & $E$1,C6:C58).

 

[Brianyo]

Try Sum(if(range,<cell,range) and press Ctrl shift and enter together ( this formaula is an array and requires the ctrl shift enter.

 

[Brianyo]

Don't know what happened last time

Try

=Sum(if(A6:A58<$E$1,C6:C58) Now press ctrl shift and enter together to make the array formula work

 

[L3Coronati]

Brian and All thanks for the help - the array solved the problem