 # Excel Count Cells if another range contains a word

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

4 Replies

# Re: Excel Count Cells if another range contains a word

@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.

# Re: Excel Count Cells if another range contains a word

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

# Re: Excel Count Cells if another range contains a word

@Riny_van_Eekelen Agreed

Since I never use direct cell references, my version read

``= SUMIFS(values, text, "MPI" )``

# Re: Excel Count Cells if another range contains a word

As variant

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