Excel Count Cells if another range contains a word

Question

Hi,&nbsp;&nbsp;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.&nbsp;&nbsp;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?&nbsp;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.&nbsp;&nbsp;&nbsp;I would be super grateful for any help. Sorry if I haven't explained myself clearly enough!&nbsp;&nbsp;Thanks&nbsp;&nbsp;

riny_van_eekelen · Answer

Vicky285&nbsp;The basic formula in B4 could be:=SUMIF($G$2:$&lt;col&gt;$2,B$3,$G3:$&lt;col&gt;3)&nbsp;..... where you replace &lt;col&gt; with the letter of the last column in the ranges on rows 2 and 3. An example &nbsp;is attached.

vicky285 · Answer

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

peterbartholomew1 · Answer

Vicky285&nbsp;Riny_van_Eekelen&nbsp;AgreedSince I never use direct cell references, my version read= SUMIFS(values, text, "MPI" )

sergeibaklan · Answer

Vicky285&nbsp;
As variant
=MMULT(G3:J3,TRANSPOSE(--(G2:J2=B3)))

Forum Discussion

Excel Count Cells if another range contains a word

4 Replies

Resources