Excel Count Cells if another range contains a word

%3CLINGO-SUB%20id%3D%22lingo-sub-1508372%22%20slang%3D%22en-US%22%3EExcel%20Count%20Cells%20if%20another%20range%20contains%20a%20word%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1508372%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20add%20up%20the%20values%20from%20one%20row%20%22IF%22%20(looking%20upwards%20along%20a%20column)%20they're%20associated%20with%20a%20specific%20text%20from%20another%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20how%20do%20I%20calculate%20the%20sum%20of%20values%20in%20row%204%20that%20has%20the%20text%20%22MPI%22%20listed%20in%20row%202%20for%20their%20column%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20the%20screenshot%20you%20can%20calculate%20this%20as%20308%20%2B%200%20%2B%204%20%2B0%20%3D%20312.%20I'm%20just%20not%20sure%20how%20to%20use%20a%20formula%20to%20do%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Vicky285_1-1594201731167.png%22%20style%3D%22width%3A%20702px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204068i1E5E9EA17B7859B8%2Fimage-dimensions%2F702x86%3Fv%3D1.0%22%20width%3D%22702%22%20height%3D%2286%22%20title%3D%22Vicky285_1-1594201731167.png%22%20alt%3D%22Vicky285_1-1594201731167.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20super%20grateful%20for%20any%20help.%20Sorry%20if%20I%20haven't%20explained%20myself%20clearly%20enough!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1508372%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1508497%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Count%20Cells%20if%20another%20range%20contains%20a%20word%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1508497%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722095%22%20target%3D%22_blank%22%3E%40Vicky285%3C%2FA%3E%26nbsp%3BThe%20basic%20formula%20in%20B4%20could%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIF(%24G%242%3A%24%242%2CB%243%2C%24G3%3A%243)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B.....%20where%20you%20replace%20%20with%20the%20letter%20of%20the%20last%20column%20in%20the%20ranges%20on%20rows%202%20and%203.%20An%20example%20%26nbsp%3Bis%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1508526%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Count%20Cells%20if%20another%20range%20contains%20a%20word%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1508526%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BOh%20wow%2C%20that%20works!%20Thank%20you%20so%20much%20for%20your%20help%20-%20I've%20been%20scratching%20my%20head%20on%20this%20for%20hours.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1508527%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Count%20Cells%20if%20another%20range%20contains%20a%20word%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1508527%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722095%22%20target%3D%22_blank%22%3E%40Vicky285%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BAgreed%3C%2FP%3E%3CP%3ESince%20I%20never%20use%20direct%20cell%20references%2C%20my%20version%20read%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20SUMIFS(values%2C%20text%2C%20%22MPI%22%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1509323%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Count%20Cells%20if%20another%20range%20contains%20a%20word%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1509323%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722095%22%20target%3D%22_blank%22%3E%40Vicky285%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMMULT(G3%3AJ3%2CTRANSPOSE(--(G2%3AJ2%3DB3)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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. 

 

Vicky285_1-1594201731167.png

 

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

 

Thanks

 

 

4 Replies
Highlighted

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

Highlighted

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

Highlighted

@Vicky285 

@Riny_van_Eekelen Agreed

Since I never use direct cell references, my version read

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

@Vicky285 

As variant

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