Help using SUMIF with a Variable

L3Coronati

New Member
Joined
Dec 16, 2005
Messages
4
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
 

Some videos you may like

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
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
Try this

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

Brianyo

Board Regular
Joined
Feb 21, 2005
Messages
91
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
Joined
Feb 21, 2005
Messages
91
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,169
Messages
5,570,638
Members
412,334
Latest member
ExcelForLifeDontHate
Top