How to refer to a criterie in a cell using excel sum.if function

[RuneVSP]

Hi i cant figure out how to makte this happen.

i have added the following

=SUM.IF(A17:A21;"<5";B17:B21)

which Work perfectly fine, the problem is that i would like to have
cell reference (B14)instead of 5 but i dont know how to make it Work.

my best guess is

=SUM.HVIS(A17:A21;"B14";B17:B21)

what am i doing wrong

 

[Claus Busch]

Hi Rune,

Am Wed, 13 Nov 2013 10:02:31 +0000 schrieb RuneVSP:

=SUM.HVIS(A17:A21;"B14";B17:B21)

put your cell reference into the formula without the quotes:
=SUMIF(A17:A21,B14,B17:B21)

For cells < B14 try:
=SUMIF(A17:A21,"<"&B14,B17:B21)


Regards
Claus B.

 

[Kevin@Radstock]

Hi i cant figure out how to makte this happen.

i have added the following

=SUM.IF(A17:A21;"<5";B17:B21)

which Work perfectly fine, the problem is that i would like to have
cell reference (B14)instead of 5 but i dont know how to make it Work.

my best guess is

=SUM.HVIS(A17:A21;"B14";B17:B21)

what am i doing wrong?

Maybe!

=SUMIF(A17:A21;"<"&B14;B17:B21

 

[RuneVSP]

Maybe!

=SUMIF(A17:A21;"<"&B14;B17:B21)

Yey, it worked.Thanks.

Now that im talking to such a smart person do you know if I coul
somehow add a range instead. ( this is actually just a small part o
trying to take the average of a given (and changeable) interval iin som
coloumn