Sumif less than x
 

Hi, I am trying to do a sumif where the criteria is less than a certain number

EG
row/col A B C
1 Month Value 3
2 1 50
3 2 75
4 3 100
5 4 200
6 5 300

If I enter the formula =sumif(A1:A6,"<3",B1:B6) then it works fine with the
answer being 125.

If I enter the formula =sumif(A1:A6,c1,B1:B6) then it works fine with the
answer being 100.

However I need the formula to refer to any months lower than the number in
cell C1 and if I enter the formula =sumif(A1:A6,"<c1",B1:B6) the cell returns
an answer of 0.

Does anyone know what I am doing wrong please?

Sumif less than x
 

Try this

=SUMPRODUCT((A2:A6<C1)*(B2:B6))

Thanks


"Michael" wrote in message
...
Hi, I am trying to do a sumif where the criteria is less than a certain
number

EG
row/col A B C
1 Month Value 3
2 1 50
3 2 75
4 3 100
5 4 200
6 5 300

If I enter the formula =sumif(A1:A6,"<3",B1:B6) then it works fine with
the
answer being 125.

If I enter the formula =sumif(A1:A6,c1,B1:B6) then it works fine with the
answer being 100.

However I need the formula to refer to any months lower than the number in
cell C1 and if I enter the formula =sumif(A1:A6,"<c1",B1:B6) the cell
returns
an answer of 0.

Does anyone know what I am doing wrong please?

Sumif less than x
 

Use this syntax:
=sumif(A1:A6,"<"&c1,B1:B6)

Regards,
Fred.

"Michael" wrote in message
...
Hi, I am trying to do a sumif where the criteria is less than a certain
number

EG
row/col A B C
1 Month Value 3
2 1 50
3 2 75
4 3 100
5 4 200
6 5 300

If I enter the formula =sumif(A1:A6,"<3",B1:B6) then it works fine with
the
answer being 125.

If I enter the formula =sumif(A1:A6,c1,B1:B6) then it works fine with the
answer being 100.

However I need the formula to refer to any months lower than the number in
cell C1 and if I enter the formula =sumif(A1:A6,"<c1",B1:B6) the cell
returns
an answer of 0.

Does anyone know what I am doing wrong please?

Sumif less than x
 

Thank you very much

"Fred Smith" wrote:

Use this syntax:
=sumif(A1:A6,"<"&c1,B1:B6)

Regards,
Fred.

"Michael" wrote in message
...
Hi, I am trying to do a sumif where the criteria is less than a certain
number

EG
row/col A B C
1 Month Value 3
2 1 50
3 2 75
4 3 100
5 4 200
6 5 300

If I enter the formula =sumif(A1:A6,"<3",B1:B6) then it works fine with
the
answer being 125.

If I enter the formula =sumif(A1:A6,c1,B1:B6) then it works fine with the
answer being 100.

However I need the formula to refer to any months lower than the number in
cell C1 and if I enter the formula =sumif(A1:A6,"<c1",B1:B6) the cell
returns
an answer of 0.

Does anyone know what I am doing wrong please?