Using SUMIF Function with a named cell reference as value in CRITE

[smilingindigo]

I am trying to use a literal cell reference as a part of the criteria in the
SUMIF function, and i can't get it to sum properly. It only seems to like an
actual value, not a cell reference, to work properly. Does anyone know a
roundabout for this?

Sample table:

A B C
1 2
2 100 5
3 15 5
4 10 4
5 5 4
6 15 3
7 10 3
8 5 2
9 25 2
10 10 1
11 3 0

SUMIF(B2:B11,">=2",A2:A11) works fine
SUMIF(B2:B11,">=$C$1",A2:All) doesn't work, returns a value of 0 in another
spreadsheet (not the same cell values as the example, but the same
structure). I want to use C1 so i can change the criteria in the SUMIF
without having to change each and every reiteration of the SUMIF i have in
different subsections of the worksheet. C1 can equal 0, 1, 2, 3, 4, 5.

any recommendations? have you run into this issue before?

 

[bpeltzer]

Try =SUMIF(B2:B11,">="&$C$1,A2:A11)
With the $C$1 in quotes, it's treated as a literal, but you need to have the
cell reference evaluated.

 

[Biff]

Hi!

Try it this way:

=SUMIF(B2:B11,">="&$C$1,A2:A11)

Biff

 

[Max]

Try it as: =SUMIF(B2:B11,">="&$C$1,A2:A11)