# Help using SUMIF with a Variable

#### L3Coronati

##### New Member
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Norie

##### Well-known Member
Try this

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

#### Brianyo

##### Board Regular
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

##### Board Regular
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

##### New Member
Brian and All thanks for the help - the array solved the problem

Replies
9
Views
272
Replies
2
Views
103
Replies
9
Views
171
Replies
24
Views
426
Replies
4
Views
243