Sumif and concatenate

[frankie6677]

Folks,
I have been using the CONCATENATE function to turn SUMIF into a relative adress capable tool. It turns the function into a pretty useful tool but it seems cumbersome to me.
Any one know a smarter way around this?
Thanks.
Francois.

Ex.: sumif("b2:b200",concatenate("=",c2),"c2:c200")

[Kieran]

frankie6677,

Teh formula you have posted is equivalent to
=sumif(b2:b200,c2,c2:c200) I think.

Maybe I am missing the point, can you explain what you are trying to achieve?

[davesexcel]

How does that formula even work?

[frankie6677]

Kieran,
I think this is a part of what I whished to acheive.
I thought you needed to have an equation in the criterion section that included an arithmetic expression.
Taking it a bit further, how would we use ">" instead of "="?
As in: =sumif(b2:b200,concatenate(">",c2),d2:d200)
Using =sumif(b2:b200,>c2,d2:d200) would not work as XL would consider"c2" as a value instead of as a reference.
The concatenate version turns the fixed condition into a relative one.
I may have overlooked something again here...

Thanks Kieran, you have simplified my work here!
Frankie

[martindwilson]

=SUMIF(B1:B9,">"&C2,D1:D9) is the usual way to ref cell

[frankie6677]

Thanks
I really learned new things today.
This is a very great forum.
Fr.