Sumifs with Variable range in criteria

[HendrikP]

I'm looking for a way to use the sumifs function with the criteria refrencing to a different cell, but also being a range.

Basicaly I want to change the formula :
=sumifs(a2:a10,b2:b10,"<10"), but instead of using <10 as a criteria, it must be refer to a cell (like <$C$2).

I've tried =sumifs(a2:a10,b2:b10,"<$C$2")
and =sumifs(a2:a10,b2:b10,<$C$2),
but to no avail.

Thanks

 

[island dan]

Hi HendrikP

The code should be written as below.

=SUMIF(A2:A10,"<"&$C$2,B2:B10)

Regards

Dan

 

[Norie]

How about trying SUMPRODUCT?

=SUMPRODUCT(--(A2:A10 < C2),B2:B10)

Note remove the spaces around the less than sign.

I forgot the board software didn't like that and I can't remember how to post formulas including it.

 

[xld]

Use

=SUMIFS(A2:A10,B2:B10,"<"&$C$2)

But actually uou only need SUMIF for one condition,

=SUMIF(A2:A10,"<"&$C$2,B2:B10)

and this will be backward compatible with Excel 2003

 

[HendrikP]

Thanks island dan! You might have just gotten me a promotion...

 

[xld]

You get a promotion for a simple SUMIF formula? Wow!