I want to sum up a value from all cells in column H, where the value in K is bigger than the value in F. I thought I could do this like that:

=SUMIFS(Gains!H:H;Gains!E:E;"sold";Gains!K:K;">="&Gains!F:F)

But the last criterion is problematic:

Gains!K:K;">="&Gains!F:F

How can I express this correctly?

CodePudding user response：

This will do the job (adjust ranges to whole column)

=SUMPRODUCT((Gains!$E$1:$E$30="sold")*(Gains!$K$1:$K$30>=Gains!$F$1:$F$30)*($H$1:$H$30))

CodePudding user response：

In newer versions =SUM(H:H*(K:K>=F:F)*(E:E;"sold")

if all these cells are in Gains sheet, could rewrite:

=SUM(Gains!H:H*(Gains!K:K>=Gains!F:F)*(Gains!E:E;"sold")