Excel Count Cells if another range contains a word

[Vicky285]

Hi, 

I need to add up the values from one row "IF" (looking upwards along a column) they're associated with a specific text from another row. 

For example, how do I calculate the sum of values in row 4 that has the text "MPI" listed in row 2 for their column?

From the screenshot you can calculate this as 308 + 0 + 4 +0 = 312. I'm just not sure how to use a formula to do this. 

I would be super grateful for any help. Sorry if I haven't explained myself clearly enough! 

Thanks

[#2]

@Vicky285 The basic formula in B4 could be:

=SUMIF($G$2:$<col>$2,B$3,$G3:$<col>3)

 ..... where you replace <col> with the letter of the last column in the ranges on rows 2 and 3. An example  is attached.

[#3]

@Riny_van_Eekelen Oh wow, that works! Thank you so much for your help - I've been scratching my head on this for hours. 

[#4]

@Vicky285 

@Riny_van_Eekelen Agreed

Since I never use direct cell references, my version read

= SUMIFS(values, text, "MPI" )

[#5]

@Vicky285 

As variant

=MMULT(G3:J3,TRANSPOSE(--(G2:J2=B3)))