CODEWITHSUNDEEP
excel
Kyle Creeden
Kyle Creeden

Reputation: 11

SUM/IF Statement with Text input and a Numeric Sum output in Excel

Ok, I am having trouble with this equation. I have (4) rows for text entries that have a numeric association depending on what you input. The equation I have does not SUM all four rows. With each text input of "I"=100,"P"=86,"N"=63 with a sum dependant on what was input.

=SUM(IF(A4:D4="I",100)+IF(A4:D4="P",86)+IF(A4:D4="N",63,0))

I should have the number 400 in E4 with "I" entered in A4:D4, or 344 in E4 with "P" entered in A4:D4.

Thank you for any help you can give.

KC

Upvotes: 1

Views: 974

Answers (2)

Dave
Dave

Reputation: 316

You can also do this with an "Array" Formula like so

=SUM((A1:D1="I")*100,(A1:D1="P")*86,(A1:D1="N")*63)

You have to enter this with Ctrl-Shift and Enter, and it will be enclosed in curly brackets.

Look up "Array Formulas" for details, they're a pain to get your head around but will save you a lot of extra columns.

Can take a while to calculate on a large sheet mind.

Upvotes: 2

Romain
Romain

Reputation: 6430

You can insert values in adjacent cells: (I will transpose the data for simplicity of formatting) 

   |  A  |  B  |
1  |  I  | =IF(A1="I";100;IF(A1="P";86;IF(A1="N";63;0)))
2  |  I  | =IF(A1="I";100;IF(A1="P";86;IF(A1="N";63;0)))
3  |  I  | =IF(A1="I";100;IF(A1="P";86;IF(A1="N";63;0)))
4  |  I  | =IF(A1="I";100;IF(A1="P";86;IF(A1="N";63;0)))

Then in E4 you have : SUM(A1:A4)

Upvotes: 0

Related Questions