expression: get value from row where x=y

[ni_avoss]

hi there,

i need to peek some data within one table and don't know how to continue. Explains best on an example:

Product	ProductGroup	Units	UnitsGroup
GroupA	GroupA	30	30
A	GroupA	10	30
B	GroupA	10	30
C	GroupA	10	30
GroupD	GroupD	40	40
D	GroupD	20	40
E	GroupD	20	40

ok, so in one column(UnitsGroup) I want to see the Values for a Group and my approach is looking for values where Product=ProductGroup

I start with =if(Product=ProductGroup,sum(Units),)

and now I am stucked with the ELSE

any ideas?

thanks in advance and best regards

[Gysbert_Wassenaar]

How about sum(total <ProductGroup> Units)/2 instead of your if statement?

[ni_avoss]

hi, thanks for you reply.

well, your idea is good.
but my example wasn't that good, as the sum of a group consist also of data, which is not in the table, why "/2" doesnt work. lets assume in my given example UnitsGroup are not 30 and 40 but 50 and 70 or so...

[Gysbert_Wassenaar]

But UnitsGroup will always be the maximum value over the ProductGroup? If so use this: max(total <ProductGroup> Units)

[ni_avoss]

well, this might sound a little bit strange but no, necessarily it's not the maximum (due to other reasons).

well, I finally found a very specific workaround.

But I would be interesited anyway, if there is some solution or possibility like "peek" in an expression