How To Use Index Match To Return A Sum of Range Given Criteria

[PaddyP]

Hello,

Can someone help me with this problem please. Cells B3 & B4 are input cells and what I would like to return is cell B5.

In the attached example, I need to total the amounts in the column that match the one identified in B3 given the Row E is greater than B4.

I hope that is clear enough.

Thank you!

[JBeaucaire]

This formula will do that:

=SUMPRODUCT(($F$3:$O$3=B3)*($E$4:$E$10>=B4)*($F$4:$O$10))

[oeldere]

C3 =Match(B3,$E$3:$O$3,0)+4

C4 =Match(B4,$E$1:$E$10,1)+1

C5 = 10 => 10 rows

C8 =SUM(INDIRECT(ADRESS(C4,C3)&":"&ADRESS(C5,C3)))

See the attached file.

[Fluff13]

How about
=SUMPRODUCT((F3:O3=B3)*(E4:E10>=B4)*(F4:O10))

[Limor_OP]

In B5:

=SUM(OFFSET($F$3,MATCH(ROUNDUP($B$4,0),$E$4:$E$10,0),MATCH(B3,$F$3:$O$3,0)-1,COUNTIF($E$4:$E$10,">"&$B$4)))

[PaddyP]

Wow so many great solutions!Thanks to everyone for the quick reply!

I see a few differences in the results but I got what I need.

Thank you!

[PaddyP]

Special shout out goes your way as this is the one that works perfectly for my data set! Thank you!

Forgot to quote below

[PaddyP]

C3 =Match(B3,$E$3:$O$3,0)+4

C4 =Match(B4,$E$1:$E$10,1)+1

C5 = 10 => 10 rows

C8 =SUM(INDIRECT(ADRESS(C4,C3)&":"&ADRESS(C5,C3)))

See the attached file.

Special shout out goes your way as this is the one that works perfectly for my data set! Thank you!

[Fluff13]

You're welcome & thanks for the feedback.