CODEWITHSUNDEEP
excelexcel-formula
Anto
Anto

Reputation: 4305

Put in a cell the sum of specific cells if other cells contains a specific text

I am a novice in Excel. I want to have a formula for the following condition (shown in line 20 in the image):

AF is the sum of the columns in red (D, H and L) only if the associated column on their right (E, I and M) contains the letter 'X'. In the image below the sum is 700 because only I and M contains X. If E would contains 'X' the sum would it be 1050.

enter image description here

Upvotes: 2

Views: 1565

Answers (3)

Scott Craner
Scott Craner

Reputation: 152660

Use SUMIF:

=SUMIF(E20:M20,"x",D20:L20)

Upvotes: 1

Solar Mike
Solar Mike

Reputation: 8415

If you would consider using 1 instead of X, then it becomes much simpler :

=D20*E20+H20*I20+L20*M20

So, E20 is 0 then I20 is 1 and M20 is 1.

Edit based on Richard's excellent suggestion which means you keep the X's as original:

=D20*ISTEXT(E20)+H20*ISTEXT(I20)+L20*ISTEXT(M20)

WARNING, this will assume ANY text X, Z, G, will be true so will get counted...

Upvotes: 1

Grosi
Grosi

Reputation: 152

=SUM(IF(E20="x",D20,0),IF(I20="x",H20,0),IF(M20="x",L20,0))

Copy this code to cell AF20, then extend it.

Upvotes: 1

Related Questions